My requirement is i need to pick up the latest SUM of FactActiveEmployeeCount based upon the Given Date. Basically its a Semi Additive measure and Measure is calculating from ETL end. Now we need to show the Active Employee Counts whenever Date selected in Tabular Cube, Based on that date Sum (Active Employee Count) should show between effective and Expiry Date.
Employee Table EmployeeKey
Its an Detail Table Supports type2 Data, for every new event it capture new record and the Last Record is the Latest record, same will enter in Fact Table as ActiveEmployeeCount as 1 And Expiry date will add as 9999-12-31
Employee1
Date Table - DateKey -- Year, Month, Quarter, Day
Fact Table EmployeeKey, DateKey, ActiveEmployeeCount, EffectiveDate, ExpairyDate
For Each Employee active Record Count shows as 1 in the Fact Table
In SQL we can get the Latest Records Writing Condition like below
EmployeeKey |
EmployeeID |
DateKey |
TranseffectiveDT |
TransExpairyDT |
EmployeeCount |
1 |
1 |
20140101 |
2014-01-01 |
2014-01-31 |
1 |
2 |
1 |
20140201 |
2014-02-01 |
2014-06-24 |
1 |
3 |
1 |
20140625 |
2014-06-25 |
9999-12-31 |
1 |
4 |
2 |
20150101 |
2015-01-01 |
2015-01-31 |
1 |
5 |
2 |
20150201 |
2015-02-01 |
2015-06-24 |
1 |
6 |
2 |
20150625 |
2015-06-25 |
9999-12-31 |
1 |
Total Employee Count is more than 200000 with Active as of current Day.
As per Above Example in Sql we can achieve this as below
Select Sum (ActiveEmployeeCount) From Fact Table
Where 2014-01-01 between EffectiveDate and ExpairyDate
It will fetch the First record of the above table
Next If we can select the date of 3rd Or 6th record. It will Fetch 2 records 3rd and 6th. As Expiry date is 9999-12-31.
Based on the given dates, it should pick the latest record accordingly and Show the records.
Please suggest how to achieve this Requirement Using DAX in Tabular Model
And Need to Achieve this in Tabular Cube only. As Users will Connect Cube via Excel and Do the Analysis...
- Edited by SSAS BI 1 hour 13 minutes ago