topic Re: How to show correct YTD values in DAX Commands and Tips

How to show correct YTD values

Anonymous — Wed, 27 Apr 2022 23:06:05 GMT

Hello all, I could use some help with getting this DAX to work as expected. What I'm trying to do is simple - return the values from the prior year-to-date. So the most recent month I have in the data is March 2022. I would expect the current YTD to be Jan+Feb+Mar 2022, just as the prior YTD would be Jan+Feb+Mar 2021. No matter what I do, may DAX is either returning numbers that I have no idea where its coming from, or a single accurate value for all months, but this doesn't work when I try to use a slicer to choose a different time period, because its the same number.

What also might be complicating this is I'm attempting to display this single value in a card visual. PBI seems to be giving me either a single value that will not change or the grand total value of the entire previous year.

Here is an example of the full list of numbers that I would expect it to be:

(Note, this is based off a formula, not an average)

So if I focus purely on Jan-Mar:

I would expect the Prior YTD to be 16.6%.

Problem #1 Accurate value but slicers don't affect it:

No matter what date/month I choose here, it returns the 16.6%, which is accurate only for a single month (the Jan+Feb+Mar timeline). Here is the code that I used to generate this: (sorry, but PBI really doesn't like my DAX code inside the code sample)

VAR prevDate =
MAXX (
ALL ( Date_Continuous[Date] ),
DATEADD ( Date_Continuous[Date], -1, YEAR )
)
VAR prevEnd =
EOMONTH ( prevDate, 0 )
VAR prevStart =
DATE ( YEAR ( prevDate ), 1, 1 )

RETURN
CALCULATE (
DIVIDE (
SUM ( Numerator ),
SUM ( Denominator )
),
Date_Continuous[Date] >= prevStart
&& Date_Continuous[Date] <= prevEnd
)

Problem #2 value that changes with slicers but is innaccurate:

This method changes the value when I select different months, but the 8.6% value is not accurate. I have no idea where it is coming from! Here is the code for this measure:

CALCULATE (DIVIDE ( SUM ( Numerator ), SUM ( Denominator ) ), DATESYTD ( DATEADD ( Date_Continuous[Date], -1, YEAR ) ) )

I played around with the -1 which is the number of intervals, but I just can't get it to work. I feel that I'm close with this method though!

Any help would be greatly appreciated!

Re: How to show correct YTD values

Whitewater100 — Thu, 28 Apr 2022 00:43:39 GMT

Hi:

I built out an example for you including YTD & PYTD on both a interger field and a percentage field. Using a nice Date Table can help alot. You can see how the measures build upon eachother (branching). I hope this can be a solution for you.

https://drive.google.com/file/d/1uNb3cBh4l89_EtPAZpjZayYAH2WbtNfJ/view?usp=sharing

I will paste a couple measure below, all are in this file.

YTD Profit Margin % =

VAR toYTDprofit = [YTD Sales] - [YTD Cost]

Prior YTD Profit Margin % = CALCULATE([YTD Profit Margin %], DATEADD(Dates[Date],-1,YEAR))

Return

DIVIDE(toYTDprofit, [YTD Sales])

DAX has more than one way to do this. Usually either DATEADD or SAMEPERIODLASTYEAR are good function to help with prior year views.

Re: How to show correct YTD values

Anonymous — Thu, 28 Apr 2022 14:50:42 GMT

Whitewater100
Thank you! Your file was very helpful

Re: How to show correct YTD values

Anonymous — Thu, 28 Apr 2022 21:56:43 GMT

Whitewater100
One more question related to this. Is there a known problem with moving measures from a table visual into a card visual? For example, from your .pbix file -

My expectation here is that the 55.4% would be in the card and the 2022 Prior YTD Proft Margin % colummn.

Re: How to show correct YTD values

Whitewater100 — Fri, 29 Apr 2022 00:48:24 GMT

Hi:

Thank you for the good words. The measures would always (unless you play with visual interactions) provide just one value based on your date slicer. So if you are looking at YTD or LY YTD and your date slicer is on 3-31-2022 - your answers will be that YTD values as of 3-31-2022.

I hope that answers your question. Thanks again..