question

ByronRoland-4490 avatar image
0 Votes"
ByronRoland-4490 asked LukasYu-msft commented

YTD Calculation on Switch Statement Not Working

I have a few measures that I am using in a Switch statement in order create a measure like this:

GraphicalMoney:=Switch([FiscalPeriod], 0, [CarryoverMoney], 1, [ActualMoney]-[CarryoverMoney], [ActualMoney])

Where [FiscalPeriod] s a value 0-12 (1 for each month plus 0 is carryover)
So, in FiscalPeriod 1, we want to subtract the carryover from actual because of the way the data exists

That measure works fine for us, the problem comes when we try to calculate a YTD amount based on that measure like this:

YTDGraphicalMoney:=TOTALYTD([GraphicalMoney], [EndDate], "9/30")

Where [EndDate] is the date associated with the records and 9/30 is that last day of the year for us.

This YTD calculation doesn't work correctly for us. FiscalPeriod 0 is correct and FiscalPeriods 2-12 are all correct, but for some reason FiscalPeriod 1 for the YTD calculation is wrong, it's just displaying the [GraphicalMoney] amount for that period instead of what I would expect YTD to calculate which is the sum of period 0 and period 1 at that point. The weird thing is periods 2-12 are correctly aggregating like I would expect them to.

sql-server-analysis-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm thinking the YTD calculation is being thrown off because of the Switch statement, but I've tried using IF statements instead to no avail.

0 Votes 0 ·
LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft commented

I didnot find many clues from the DAX expression. I suspect this happens because of the starting month of your fiscal year.

Does FiscalPeriod 2-12 also need to substrace period 0 amount ?


You could check if you model structure is correct. Check when you do YTD directly on the ActualMoney measure, was it giving you the correct answer ?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you made any test ? As we guested, what is possible is that the relationship or struture related with fiscal calandar, hope we could find any clue there.

0 Votes 0 ·
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered

The problem is possibly in the [FiscalPeriod] measure. What is the logic you have in that measure?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.