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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RP101
Frequent Visitor

YoY Variance Report by Month with dynamic header

Hi,

 

I have created a Year-over-Year (YoY) variance report by month and would like to enhance it with the following features:

  1. Add a dynamic header when a year is selected. I tried creating dynamic fields, but I am getting a blank value, and it ends up returning the variable itself as the header.
  2. Add a Year-to-Date (YTD) total before the Total row. This should display the total for the previous year, the current year, and the YoY change in units up to yesterday.
  3. I want to also see the Totals as is. 

YoY Variance pbix file 

RP101_0-1734148686679.png

 

 

Table = 

Client IDOrder DateItem NumberClient NameClient Request TypeUnits

1500029/1/2024ORT123ToysRUs CorporationAdd1
1400019/21/2024ABD123Amazon ABC LLCAdd1
1400019/22/2024ABC124Amazon XNY LLCAdd1
1500027/1/2024ORT123ToysRUs CorporationAdd1
1400017/21/2024ABD123Amazon ABC LLCAdd1
1400017/22/2024ABC124Amazon XNY LLCAdd1
1500028/1/2024ORT123ToysRUs CorporationAdd1
1500029/1/2023ORT123ToysRUs CorporationAdd1
1400019/21/2023ABD123Amazon ABC LLCAdd1
1400019/22/2023ABC124Amazon XNY LLCAdd1
1500027/1/2023ORT123ToysRUs CorporationAdd1
1400017/21/2023ABD123Amazon ABC LLCAdd1
1400017/22/2023ABC124Amazon XNY LLCAdd1
1500028/1/2023ORT123ToysRUs CorporationAdd1
1400018/21/2023ABD123Amazon ABC LLCAdd1
1400018/22/2023ABC124Amazon XNY LLCAdd1
1500028/1/2023ORT123ToysRUs CorporationAdd1
1700039/1/2024AOL123Ross IncCancel Order1
1800059/6/2024PAS123T-Mobile NJ LLCCancel Order1
1800059/6/2024PAS124T-Mobile NY LLCCancel Order1
1700037/1/2024AOL123Ross IncCancel Order1
1800057/6/2024PAS123T-Mobile NJ LLCCancel Order1
1800057/6/2024PAS124T-Mobile NY LLCCancel Order1
1700039/1/2023AOL123Ross IncCancel Order1
1800059/6/2023PAS123T-Mobile NJ LLCCancel Order1
1800059/6/2023PAS124T-Mobile NY LLCCancel Order1
1700037/1/2023AOL123Ross IncCancel Order1
1800057/6/2023PAS123T-Mobile NJ LLCCancel Order1
1800057/6/2023PAS124T-Mobile NY LLCCancel Order1
1700038/1/2023AOL123Ross IncCancel Order1
1800058/6/2023PAS123T-Mobile NJ LLCCancel Order1
1800058/6/2023PAS124T-Mobile NY LLCCancel Order1
2222229/21/202468562ATargetCreate Order1
2121219/22/202474432BMacysCreate Order1
2222227/21/202468562ATargetCreate Order1
2121217/22/202474432BMacysCreate Order1
2020208/26/202453620ALowesCreate Order1
2020208/27/202440058FHome DepotCreate Order1
2222228/21/202468562ATargetCreate Order1
2121218/22/202474432BMacysCreate Order1
2020208/26/202453620ALowesCreate Order1
2020208/27/202440058FHome DepotCreate Order1
2222229/21/202368562ATargetCreate Order1
2121219/22/202374432BMacysCreate Order1
2222227/21/202368562ATargetCreate Order1
2121217/22/202374432BMacysCreate Order1
2222228/21/202368562ATargetCreate Order1
2121218/22/202374432BMacysCreate Order1
2020208/26/202353620ALowesCreate Order1
2020208/27/202340058FHome DepotCreate Order1
2222228/21/202368562ATargetCreate Order1
2121218/22/202374432BMacysCreate Order1
2020208/26/202353620ALowesCreate Order1
2020208/27/202340058FHome DepotCreate Order1
1500028/1/2024ORT123ToysRUs CorporationLate Fee1
1400018/21/2024ABD123Amazon ABC LLCLate Fee1
1400018/22/2024ABC124Amazon XNY LLCLate Fee1
1500028/1/2024ORT123ToysRUs CorporationLate Fee1
1400018/22/2024ABC124Amazon XNY LLCLate Fee1
1800058/6/2024PAS123T-Mobile NJ LLCLate Fee1
1800058/6/2024PAS124T-Mobile NY LLCLate Fee1
1500028/1/2023ORT123ToysRUs CorporationLate Fee1
1400018/21/2023ABD123Amazon ABC LLCLate Fee1
1400018/22/2023ABC124Amazon XNY LLCLate Fee1
1500028/1/2023ORT123ToysRUs CorporationLate Fee1
1400018/22/2023ABC124Amazon XNY LLCLate Fee1
1800058/6/2023PAS123T-Mobile NJ LLCLate Fee1
1800058/6/2023PAS124T-Mobile NY LLCLate Fee1
1700038/1/2024AOL123Ross IncModify1
1800058/6/2024PAS123T-Mobile NJ LLCModify1
1800058/6/2024PAS124T-Mobile NY LLCModify1
1500028/1/2024ORT123ToysRUs CorporationModify1
1400018/21/2024ABD123Amazon ABC LLCModify1
1400018/22/2024ABC124Amazon XNY LLCModify1
1700038/1/2023AOL123Ross IncModify1
1800058/6/2023PAS123T-Mobile NJ LLCModify1
1800058/6/2023PAS124T-Mobile NY LLCModify1
1500028/1/2023ORT123ToysRUs CorporationModify1
1400018/21/2023ABD123Amazon ABC LLCModify1
1400018/22/2023ABC124Amazon XNY LLCModify1

 

 

Calendar =
Var Days = CALENDAR( MIN('Table'[Order Date]),MAX('Table'[Order Date]) )
RETURN ADDCOLUMNS(
    Days,
    "Year", YEAR([Date]),
    "Previous Year", YEAR ( [Date] )-1,
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "mmmm"),
    "Year Month Number", YEAR([Date]) * 12 + MONTH([Date]) - 1,
    "Year Month", FORMAT([Date], "mmm yy"),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Week Number", WEEKNUM([Date]),
    "Week Number of Year",WEEKNUM([Date],21),
    "Week Number and Year", "W" & WEEKNUM([Date]) & " " & YEAR([Date]),
    "WeekYearNumber", YEAR([Date]) & 100 + WEEKNUM([Date]),
    "Is working Day", not WEEKDAY([Date]) in {1,7},
    "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)
 
Current Year = YEAR(TODAY())
 
CurrYear =
CALCULATE (
    SUM('Table'[Units]),DATEADD('Calendar'[Date],0,YEAR)  
) + 0
 
PreYear =
CALCULATE (
    SUM('Table'[Units]),DATEADD('Calendar'[Date],-1,YEAR)  
) + 0
 
YoY % =
VAR CurrentYearValue = CALCULATE (
    SUM('Table'[Units]),DATEADD('Calendar'[Date],0,YEAR)  
) + 0

VAR PreviousYearValue = CALCULATE (
    SUM('Table'[Units]),DATEADD('Calendar'[Date],-1,YEAR)  
) + 0

RETURN
IF(
    NOT ISBLANK(PreviousYearValue),
    SWITCH(
        TRUE(),
        PreviousYearValue > 0 && CurrentYearValue = 0, -1, -- Case for previous value > 0 and current value = 0
        PreviousYearValue = 0 && CurrentYearValue > 0, 1,  -- Case for previous value = 0 and current value > 0
        PreviousYearValue = 0 && CurrentYearValue = 0, 0.00, -- Case for both values being 0
        DIVIDE(CurrentYearValue - PreviousYearValue, PreviousYearValue) -- Default case using DIVIDE
    ),
    BLANK()
)
 
 RP101_2-1734152229217.png

 

Dynamic column header measure 

Current Year = YEAR(TODAY())
Previous Year = YEAR(TODAY())-1
Dynamic Fields = {
    ( SELECTEDVALUE('Calendar'[Year]), NAMEOF('Table'[CurrYear]), 0),
    ( SELECTEDVALUE('Calendar'[Year])-1, NAMEOF('Table'[PreYear]), 1),
    ("YoY %", NAMEOF('Table'[YoY %]), 2)
}
 Dynamic Fields is not gettting value from selectedvalue
RP101_1-1734151843694.png
RP101_3-1734152288058.png

 

RP101_0-1734151745707.png

 

 

 

 

I would appriciate any guidance ! 

Thanks,

RP

8 REPLIES 8
RP101
Frequent Visitor

Hi @danextian 
Thanks for the guidance I was able to get the dynamic header work but the column total is blank now, can you please check the PBI matrix and suggest solution how to fix it?

YoY Variance 2.pbix


I am also getting additional column Total which I want to remove. 

 

RP101_0-1734243553747.png

Thanks in advanced !

Are these measures addedd directly to the matrix or from a disconnected table column being referenced by a conditional measure?

danextian_0-1734267764512.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian 
The measure named 'MyMeasure' was created under CustomPeriod and added to a matrix under Values. This matrix can be found on the third page of the pbix file. I attempted to use dynamic fields, but it didn't work, as shown on the second page. I hope this clarifies things. Apologies for any confusion.

danextian
Super User
Super User

hi @RP101 

Not impossible but quite tricky. Please see sample files to help you get started with.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian 

 

Thanks for the quick reply ! I will check and see if it helps. how were you able to upload pibx files I was trying wanted to upload mine but didn't see any options. 

 

Only superusers, CST and community admins can. You may post a link to files stored in the cloud though - just make sure they're publicly accessible/no need to request for permission.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian 

It did not slove my problem completly as I am not getting the column built in grand total. I didn't except that to be not working.

do you have any idea?

 

thanks

 

Hi @RP101 ,

You could check the DAX formula used for the dynamic headers and ensure it is designed to aggregate correctly at the grand total level. Additionally, consider using the HASONEVALUE function to conditionally calculate totals when all values are present.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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