Get Latest Record Measure Based on Given Date using DAX in Tabular Model

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
July 4th, 2015 1:55am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics