topic Re: Dax Measure - Lastdate in DAX Commands and Tips

Dax Measure - Lastdate

andybamber — Wed, 14 Oct 2020 15:00:14 GMT

Hello All!

I have a file of weekly data snapshots. The file has a snapshot date stamp

I have a measure:

headcount_Max_Wk = CALCULATE(DISTINCTCOUNT(SNAP_DWH_RES_RESOURCE[resource_key]),LASTDATE(SNAP_DWH_RES_RESOURCE[snaphot_stamp]))

The idea is i get a distinct count based on the latest date. When you create a bar chart and show the data by business area and year/month it correctly shows the headcount... and i can see its taking the last date for the month and giving the correct distinct headount

However on a second visual, where the bar chart is showing job role and year/month it doesnt work correctly... its overcounting this seems to be because some people have changed their job from 1 week to another, in the same month. As you can see below Name_2 and Name_3 are examples... how do i change my measure to ignore the 1st October row of data for each?

Cheers

Andy

Re: Dax Measure - Lastdate

lbendlin — Thu, 15 Oct 2020 01:36:54 GMT

For each Employee and Month Name you want to find the latest Day of the month. Once you have that you can then lookup the Job role for that user for that date.

Do you need to do this in DAX or is Power Query an option too?

Re: Dax Measure - Lastdate

Anonymous — Thu, 15 Oct 2020 04:31:39 GMT

andybamber Hi , You can use this formula . I took employee Column to get distinct count by month .

headcount_Max_Wk = CALCULATE(DISTINCTCOUNT(Sheet1[Employee]),LASTDATE(Sheet1[SnapShot Date Stamp]))

Re: Dax Measure - Lastdate

andybamber — Thu, 15 Oct 2020 06:17:19 GMT

Hi There!

Dax would be the preferred option, but i'm open to a power query solution..

Cheers

Andy

Re: Dax Measure - Lastdate

andybamber — Thu, 15 Oct 2020 06:41:56 GMT

Anonymous thanks for your reply... Apart from using Employee field in stead of resource Key (which is effectively the unique employee ID) your measure is probably the same as mine... i think maybe you require some additional information... so the Month is being pulled into the visual from the linked Date Dimension table, the join is from snapshot date stamp to the data.. does that help?

Cheers

Andy

Re: Dax Measure - Lastdate

Anonymous — Thu, 15 Oct 2020 07:24:06 GMT

andybamber : It Will Work for that as well . you have to define BI Directional Relationship in between datetable and your master table then it will work fine for you .

Re: Dax Measure - Lastdate

andybamber — Thu, 15 Oct 2020 07:36:24 GMT

Anonymous its currently set as both....

Re: Dax Measure - Lastdate

Anonymous — Thu, 15 Oct 2020 07:41:30 GMT

andybamber Can you please sample file with relevant data and kindly mask other columns.It will be great .

Re: Dax Measure - Lastdate

andybamber — Thu, 15 Oct 2020 12:51:11 GMT

Anonymous I have a file for you, how do i send as it wont let me attach a PBIX into a message....

Cheers

Andy

Re: Dax Measure - Lastdate

andybamber — Thu, 15 Oct 2020 12:57:41 GMT

Anonymous PBIX ex

Re: Dax Measure - Lastdate

andybamber — Fri, 16 Oct 2020 05:32:55 GMT

Anonymous i should also have said regarding the PBIX that the right hand bar chart is correctm showing 8 for both months, but its the one on the left with the actual role where its not counting correctly... thanks