Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Walt
Frequent Visitor

YoY Calculations and Summing filterd columns with blanked out data.

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.

Walt_0-1664466808841.png

Walt_1-1664467077033.png

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()
)

Walt_0-1664478223282.png
The total of the 2nd column should be 97,525 not 130450

Walt_1-1664478315999.png

 



1 ACCEPTED SOLUTION
Anonymous
Not applicable

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]
)

yingyinr_0-1664508303086.png

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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]
)

yingyinr_0-1664508303086.png

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

amitchandak
Super User
Super User

@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-bd52912a5b...
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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.   

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.