topic Re: Calculation Enigma in DAX Commands and Tips

Calculation Enigma

BrianNeedsHelp — Fri, 30 May 2025 16:17:33 GMT

I compute this year's daily goals based on last year/same month. I get previous year like this- 364 days previous is the same weekday so it aligns properly.

PYGA1=CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-364,DAY)))

Works great except then end of the month numbers are higher than they should be because it's calculating last year's first of the month on the last day of the month. To correct it I did this:

PYGA1 = IF('Calendar'[EndofMOnth]=DAY(SELECTEDVALUE('Calendar'[Calendar Date])),CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-371,DAY)),CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-364,DAY)))

It adjusts the last day of the month, but the total adds up to the same as it did when there was just the -364 measure. So the PYALLGAs1 is showing the same total as it did in the first measure so it doesn't adjust each day higher like it should. The goal for each day stays the same. Not sure how this is even possible? Any help greatly appreciated

PYALLGAs1 = CALCULATE([PYGA1], ALL('MyCalendar'[Date]))

GAGOAL = [PYGA1]/[PYALLGAs1]*[LocQuota1]

So the essential problem is that the sum 15,457 is not correct. If i export this and add it up it comes to 15,227.

Re: Calculation Enigma

Deku — Fri, 30 May 2025 22:38:33 GMT

Try using parallelperoid('Calendar'[Calendar Date], -1, year )

Re: Calculation Enigma

v-aatheeque — Wed, 04 Jun 2025 18:12:03 GMT

Hi BrianNeedsHelp ,

Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

Re: Calculation Enigma

BrianNeedsHelp — Wed, 04 Jun 2025 19:26:40 GMT

Tried parallel period but it put and astronomical number in each row. But I finally got the PYGA1 column to add up correctly by inputting another measure:

SUMX(SUMMARIZE(MyCalendar,MyCalendar[Date]),CALCULATE(SUMX(MyCalendar,[PYGA1])))

Re: Calculation Enigma

v-aatheeque — Thu, 05 Jun 2025 17:04:10 GMT

Hi BrianNeedsHelp ,

Thanks for the update, and well done on resolving the issue with the SUMX(SUMMARIZE(...)) approach and effective way to ensure the daily values aggregate correctly, especially when adjusting for edge cases like end-of-month alignment.

If everything looks good on your end now, we’d appreciate it if you could mark your solution as Accepted. This helps other users who may face similar challenges find guidance more easily.

Feel free to reach out if you need further assistance or want to explore additional improvements we are happy to help!!