Forum Discussion

Attrywa's avatar
Attrywa
Frequent Visitor
2 years ago

Grand total with blank

Hi,  I have a matrix table with current conversion and conversion Y-1 based on ISO years and weeks, which are selected on the filter pane by the user of the report:  The problem is that gra...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

Attrywa , You can try a measure like

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

 

of switch your measure with this measure in grand total

if(isinscope([Year ISO]), [Conversion Y-1], [Week Behind sales] )

 

Of you can Witch GT with this Measure

 

Conversion Y-1 GT =

VAR CurrentWeek =  Maxx(allselected(Date),  Date[Week ISO] )
VAR CurrentYear = Maxx(allselected(Date),  Date[Year ISO] )  
VAR a =
        CALCULATE (
            [Current conversion],
            FILTER (
                ALL ( Date ),
                Date[Year ISO] IN {CurrentYear-1} && Date[Week ISO] IN CurrentWeek )
            )
   RETURN   a
 
How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA
Attrywa's avatar
Attrywa
Frequent Visitor
2 years ago

amitchandak  Thank you for quick answer. 
I can't use Dateadd function set on a day, because the number of days in each year is not constant - in 2022 and 2023 there are 365 day, however in 2024 there are 366 days according to my date table.
Furthermore Dateadd function set on a year is also not correct, due to the fact that W02 of 2024 is 07.01 - 13.01 and W02 in 2023 is 08.01-14.01.

I have used the second option - Conversion Y-1 GT, but the result is the value from the last week of the previous year: 

And what I would need in GT is the sum of equivalent X and Y from the W01-W04 in 2023 + W51-W52 in 2022 divided by one another