DAX help

[mikeborg82]

PY YTD Terms = CALCULATE(DISTINCTCOUNT(Terms[Employee Number]),FILTER(SAMEPERIODLASTYEAR('Date Dim'[Date]),CONTAINS(VALUES('Date Dim'[Prior Year]), 'Date Dim'[Prior Year], 'Date Dim'[Date])))

I have the above dax, which I am trying to basically just find a count of employees terminated at this point last year YTD.  This formula gives me everyone for the year.. Any idea how I can adjust to get just terms YTD at the time?  Thank you!

[Ashish_Mathur]

Hi,

How about this

=CALCULATE(DISTINCTCOUNT(Terms[Employee Number]),SAMEPERIODLASTYEAR('Date Dim'[Date]))

[mikeborg82]

Here is what happens.  Last years terms should only be 545, and at this point last year should only be about 380.. But when i put into the table, it brings all terms.  I think year is out of context, or something.  just not sure how to fix.  Appreciate any help.  Thanks!

[mikeborg82]

PY Test = CALCULATE(DISTINCTCOUNT(Terms[Employee Number]),SAMEPERIODLASTYEAR(Terms[Actual Termination Date]))

It seems to work like this... when i refer to the date dimension table it doesnt seem to work, but this way it does.  Does that seem right?  could there be a reason that this is happening and maybe something i should fix upstream?  Thanks, still getting my feet wet with DAX and powerbi.

[Ashish_Mathur]

Hi,

You have to create a relationship from the Actual Termination data column of the Terms Table with the Date column of the calendar table.  If you need more specific help, share the link from where i can download your file.

[mikeborg82]

Thanks. sent you message with the link.  it is just a sample test file.  thanks again for any help!

[Ashish_Mathur · Accepted solution]

Hi,

You may see my solution here.

Hope this helps.

[mikeborg82]

i see you had to apply filters to the page.  is that the only way to handle this?

[Ashish_Mathur]

Hi,

Using filters is the easiest way to make it work.

[mikeborg82]

thank you.  this does work!  do you think you could explain what the issue was so i will know for future models?  THanks again i really appreciate the help

[Ashish_Mathur]

You are welcome.  The DATESBETWEEN() function used in the YTD terminations measure ensures that the arithmatic (distinctcount in our case) starts from the 1st day of the year and goes till the last date of the month chosen in the visual filter.

[mikeborg82]

sorry to keep this going - but is there any way to set this up a bit more dynamically?  I would rather not have to update the model each month and I can't believe that PowerBI doesn't have a better solution..

[mikeborg82]

Yes, this makes perfect sense.  I assumed when I used a function like "TotaLYTD", that powerbi would know to give me the total of the year I am in YTD at the time, and I wouldn't need to add a filter.  My date dimension table goes for several years ahead, so this could be why when I use the totalytd function with no filter, I get blank results.  

Thanks again for your help.

[mikeborg82]

sorry to keep this going - but now I run into the following error message when trying to pull in other fields into the visual.

but when i refer to the date table, i do not get the right values.  but that causes other issues like this since not referring to date table.  i must be doing something wrong, but can't seem to figure it out.

[mikeborg82]

Oddly, this did not work.  I get the same results with this calc (all of prior year), not just YTD last year at this time..

[mikeborg82]

I also tried using this pattern, but to not luck:

PY YTD Terms = CALCULATE(DISTINCTCOUNT(Terms[Employee Number]),(SAMEPERIODLASTYEAR(DATESYTD('Date Dim'[Date]))))