YoY Variance Report by Month with dynamic header

[RP101]

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 

Table = 

Client IDOrder DateItem NumberClient NameClient Request TypeUnits

150002	9/1/2024	ORT123	ToysRUs Corporation	Add	1
140001	9/21/2024	ABD123	Amazon ABC LLC	Add	1
140001	9/22/2024	ABC124	Amazon XNY LLC	Add	1
150002	7/1/2024	ORT123	ToysRUs Corporation	Add	1
140001	7/21/2024	ABD123	Amazon ABC LLC	Add	1
140001	7/22/2024	ABC124	Amazon XNY LLC	Add	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Add	1
150002	9/1/2023	ORT123	ToysRUs Corporation	Add	1
140001	9/21/2023	ABD123	Amazon ABC LLC	Add	1
140001	9/22/2023	ABC124	Amazon XNY LLC	Add	1
150002	7/1/2023	ORT123	ToysRUs Corporation	Add	1
140001	7/21/2023	ABD123	Amazon ABC LLC	Add	1
140001	7/22/2023	ABC124	Amazon XNY LLC	Add	1
150002	8/1/2023	ORT123	ToysRUs Corporation	Add	1
140001	8/21/2023	ABD123	Amazon ABC LLC	Add	1
140001	8/22/2023	ABC124	Amazon XNY LLC	Add	1
150002	8/1/2023	ORT123	ToysRUs Corporation	Add	1
170003	9/1/2024	AOL123	Ross Inc	Cancel Order	1
180005	9/6/2024	PAS123	T-Mobile NJ LLC	Cancel Order	1
180005	9/6/2024	PAS124	T-Mobile NY LLC	Cancel Order	1
170003	7/1/2024	AOL123	Ross Inc	Cancel Order	1
180005	7/6/2024	PAS123	T-Mobile NJ LLC	Cancel Order	1
180005	7/6/2024	PAS124	T-Mobile NY LLC	Cancel Order	1
170003	9/1/2023	AOL123	Ross Inc	Cancel Order	1
180005	9/6/2023	PAS123	T-Mobile NJ LLC	Cancel Order	1
180005	9/6/2023	PAS124	T-Mobile NY LLC	Cancel Order	1
170003	7/1/2023	AOL123	Ross Inc	Cancel Order	1
180005	7/6/2023	PAS123	T-Mobile NJ LLC	Cancel Order	1
180005	7/6/2023	PAS124	T-Mobile NY LLC	Cancel Order	1
170003	8/1/2023	AOL123	Ross Inc	Cancel Order	1
180005	8/6/2023	PAS123	T-Mobile NJ LLC	Cancel Order	1
180005	8/6/2023	PAS124	T-Mobile NY LLC	Cancel Order	1
222222	9/21/2024	68562A	Target	Create Order	1
212121	9/22/2024	74432B	Macys	Create Order	1
222222	7/21/2024	68562A	Target	Create Order	1
212121	7/22/2024	74432B	Macys	Create Order	1
202020	8/26/2024	53620A	Lowes	Create Order	1
202020	8/27/2024	40058F	Home Depot	Create Order	1
222222	8/21/2024	68562A	Target	Create Order	1
212121	8/22/2024	74432B	Macys	Create Order	1
202020	8/26/2024	53620A	Lowes	Create Order	1
202020	8/27/2024	40058F	Home Depot	Create Order	1
222222	9/21/2023	68562A	Target	Create Order	1
212121	9/22/2023	74432B	Macys	Create Order	1
222222	7/21/2023	68562A	Target	Create Order	1
212121	7/22/2023	74432B	Macys	Create Order	1
222222	8/21/2023	68562A	Target	Create Order	1
212121	8/22/2023	74432B	Macys	Create Order	1
202020	8/26/2023	53620A	Lowes	Create Order	1
202020	8/27/2023	40058F	Home Depot	Create Order	1
222222	8/21/2023	68562A	Target	Create Order	1
212121	8/22/2023	74432B	Macys	Create Order	1
202020	8/26/2023	53620A	Lowes	Create Order	1
202020	8/27/2023	40058F	Home Depot	Create Order	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Late Fee	1
140001	8/21/2024	ABD123	Amazon ABC LLC	Late Fee	1
140001	8/22/2024	ABC124	Amazon XNY LLC	Late Fee	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Late Fee	1
140001	8/22/2024	ABC124	Amazon XNY LLC	Late Fee	1
180005	8/6/2024	PAS123	T-Mobile NJ LLC	Late Fee	1
180005	8/6/2024	PAS124	T-Mobile NY LLC	Late Fee	1
150002	8/1/2023	ORT123	ToysRUs Corporation	Late Fee	1
140001	8/21/2023	ABD123	Amazon ABC LLC	Late Fee	1
140001	8/22/2023	ABC124	Amazon XNY LLC	Late Fee	1
150002	8/1/2023	ORT123	ToysRUs Corporation	Late Fee	1
140001	8/22/2023	ABC124	Amazon XNY LLC	Late Fee	1
180005	8/6/2023	PAS123	T-Mobile NJ LLC	Late Fee	1
180005	8/6/2023	PAS124	T-Mobile NY LLC	Late Fee	1
170003	8/1/2024	AOL123	Ross Inc	Modify	1
180005	8/6/2024	PAS123	T-Mobile NJ LLC	Modify	1
180005	8/6/2024	PAS124	T-Mobile NY LLC	Modify	1
150002	8/1/2024	ORT123	ToysRUs Corporation	Modify	1
140001	8/21/2024	ABD123	Amazon ABC LLC	Modify	1
140001	8/22/2024	ABC124	Amazon XNY LLC	Modify	1
170003	8/1/2023	AOL123	Ross Inc	Modify	1
180005	8/6/2023	PAS123	T-Mobile NJ LLC	Modify	1
180005	8/6/2023	PAS124	T-Mobile NY LLC	Modify	1
150002	8/1/2023	ORT123	ToysRUs Corporation	Modify	1
140001	8/21/2023	ABD123	Amazon ABC LLC	Modify	1
140001	8/22/2023	ABC124	Amazon XNY LLC	Modify	1

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

)

 

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

I would appriciate any guidance ! 

Thanks,

RP

[RP101]

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. 

Thanks in advanced !

[danextian]

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

[RP101]

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]

hi @RP101 

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

[RP101]

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. 

[danextian]

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.

[RP101]

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

[Anonymous]

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.