Cumulative measure with slicer that also displays dates prior to slicer (help!)

[Anonymous]

Hi, I am using our financial weeks in a date table.

The date table connects to value date via 'REFUND DATE or SALE DATE using USERRELATIONSHIP and a switch. 

I am trying to visualise cumulative (our financial year YTD) values. 

Date structure example for 2020/21 week 8 and 2019/20 week8 )

FinYear = Business Financial Year

Date_slicer_week table:

FinYearWeekValue (this is the key to the date table	FinYearWeek.Slicer (This is used in the slicer)	week value
2021.08	2021 W08	8
1920.08	1920 W08	8

Date_table table:

FinYearWeekValue (this is the key to the date_week slicer)	Financial year	week value
2021.08	2021	8
1920.08	1920	8

This are the intial measures I am using for the values

Value Calc by Refund Date = CALCULATE(
    SUM('Test_Value'[Value]),
    USERELATIONSHIP(
        Date_Table[Date],
        'Test_Value'[Refund Date]
    )
)

Value Calc by Sale Date = CALCULATE(
    SUM('Test_Value'[Value]),
    USERELATIONSHIP(
        Date_Table[Date],
        'Test_Value'[Sale Date]
    )
)

the switch measure for the value

Value Calc Switch = switch([IND Measure],1,[Value Calc by Refund Date], 2, [Value Calc by Sale Date]) //changes date relationship based on the DATE_TYPE_SWITCH SLICER

And this is the cumulative measure (ignore quarter/period for this query)

YTD Value using Selectedvalue = 
VAR CurrentWeek = SELECTEDVALUE( Date_Table[Week Value] )
VAR CurrentQuarter = SELECTEDVALUE (Date_Table[Quarter Value])
VAR CurrentPeriod = SELECTEDVALUE ( Date_Table[Period Value])
VAR CurrentYear = SELECTEDVALUE( Date_Table[FinYear Value] )

RETURN
CALCULATE( 'Measure Value'[Value Calc Switch],
    FILTER( ALL( Date_Table ),
		Date_Table[Week Value] <= CurrentWeek && Date_Table[FinYear Value] = CurrentYear ))

My measure works in the table when I have no slicer applied. Problem (1) I have is in the bar visual the Cumulative(/YTD) measure only displays if I have a 2nd filter to select a single year.

My 2nd (and main) problem I have is when I apply the week (2021 W08) slicer the values still calculate correctly but I want it to display the previous weeks. Currently it is just showing the selected week in the slicer. 

n.b. SELECTEDVALUE is a necessity so users can look back in the data history rather than using calculated columns to create ThisYearValue. 

[Anonymous]

Hi, 

I'm still stuck with this, can anyone help?

[Anonymous]

Hi @Anonymous the file is here

https://1drv.ms/u/s!Aqw4QIizueItgyk-YPcJ_oRYvkdJ?e=BYEyuC

[Anonymous]

Hi @Anonymous ,

Sorry for my late reply...

For the first question, if you want to display the value only after the slicer2 has been selected , you could use ISFILTERED() function to judge.

For the second question, if you want to display the previous week based on Slicer , you need to create a new table for Slicer which has no relationship with the Date Table.

=
CALCULATE (
    MAX ( 'Table'[Value] ),
    FILTER (
        'Date Table',
        'Date Table'[Week Value] <= SELECTEDVALUE ( 'forSlicer'[Week Slicer] )
    )
)

Best regards,

Eyelyn Qin

[Anonymous]

hi @Eyelyn9 is there anyway else for me to share this with you? I would be very appreciative of any help you can give.

[Anonymous]

hi @Anonymous 

Sure, can I have your email address please. My organisation prevents use from sharing files using the 'anyone with link' option in onedrive. 

[amitchandak]

@Anonymous , for the week vs previous week you refer to my blog. Make week /date is in a separate table and create week rank and do it.

New column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Week Till Date

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] <= Max('Date'[Week])))

[Anonymous]

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))

This is the measure structure I have but it doesn't show prior weeks when you apply a slicer. 

[Anonymous]

Hi @Anonymous ,

Could your share me with your pbix file from your Onedrive for Business?

Best Regards,
Eyelyn Qin