topic YoY Calculations and Summing filterd columns with blanked out data. in DAX Commands and Tips

YoY Calculations and Summing filterd columns with blanked out data.

Walt — Thu, 29 Sep 2022 19:17:49 GMT

The Meause I am using is working well, but in 2 months where the previous year is missing 1 weeks Values and a current month is missing 1 weeks values. When I view the YoY by month the calculation is incorrect. Any help would be great.

I was able to get the values to show up correctly with IF, however they still dont sum correctly
Here is an example of the last year values with the IF statement.
measure =

IF(
[Value] <> BLANK(),
CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(CalendarTable[Date])),
BLANK()
)

The total of the 2nd column should be 97,525 not 130450

Re: YoY Calculations and Summing filterd columns with blanked out data.

amitchandak — Fri, 30 Sep 2022 02:06:58 GMT

Walt , Simply use this , do not handle blank

CALCULATE(SUM(Table[Value]), SAMEPERIODLASTYEAR(CalendarTable[Date]))

Also make sure 6 reasons are covered

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

For not matching grand total refer

https://www.youtube.com/watch?v=ufHOOLdi_jk

Re: YoY Calculations and Summing filterd columns with blanked out data.

Walt — Fri, 30 Sep 2022 02:19:38 GMT

Thank you for your response. However the measure you responded with will sum the entire column for the month. please see the examples for an explanation of what I'm trying to accomplish.

Re: YoY Calculations and Summing filterd columns with blanked out data.

Anonymous — Fri, 30 Sep 2022 03:27:31 GMT

Hi @SewardExelon ,

You can create another new measure as below and put this new measure to replace the original measure onto the table visual as below screenshot...

Note: The groupby fields are from the fields marked with red lines in the screenshot below...

New mesaure = SUMX ( GROUPBY ( CalendarTable, CalendarTable[YearQuarter], CalendarTable[Month], CalendarTable[Week] ), [your measure name] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you get the desired result, please provide some sample data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards