Show variance based on filter selection

[Anonymous]

Hello experts!

I have created some measures to display the variance between a week and the previous one to that however I would like to be able to have the variance showing accordingly to a slicer selection. If nothing is selected then show the difference with previous week as I already have it:

Year	Quarter	Month	Day	WEEK	FCST	PREV. WK FCST	FCST DIFF vs PREV. WK
2020	Qtr 1	January	6	202002	1,861,980	1,864,549	-2,569
2019	Qtr 4	December	30	202001	1,864,549	1,904,024	-39,475
2019	Qtr 4	December	23	201952	1,904,024	1,895,539	8,485
2019	Qtr 4	December	16	201951	1,895,539	1,868,802	26,737
2019	Qtr 4	December	9	201950	1,868,802	1,855,457	13,345
2019	Qtr 4	December	2	201949	1,855,457

FCST = SUM(FCST[FCST QTY])

PREV. WK FCST = CALCULATE([FCST],DATEADD('CALENDAR'[Date],-7,DAY))

FCST DIFF vs PREV. WK = [FCST]-[PREV. WK FCST]

So, for example, if I pick week 49 and week 52 it should give me the following:

Year	Quarter	Month	Day	WEEK	FCST	PREV. WK FCST	FCST DIFF vs PREV. WK
2019	Qtr 4	December	23	201952	1,904,024	1,855,457	48,567
2019	Qtr 4	December	2	201949	1,855,457

I hope my explanation is clear enough otherwise please, let me know and I will do my best to help you so you can help me 🙂

Regards,

@Anonymous hello Cheenu, I have seen some posts with similar issues so I thought I might contact you directly with mine and see whether you can help me 🙂 

Thanks!!

[v-yuta-msft]

@Anonymous ,

Modify the measure using dax below:

PREV. WK FCST =
CALCULATE (
    [FCST],
    FILTER ( ALLSELECTED ( Table ), DATEADD ( 'CALENDAR'[Date], -7, DAY ) )
)

Community Support Team _ Jimmy Tao

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

[Anonymous]

Hello @v-yuta-msft 

For some reason it doesn't work. I am not sure whether I am doing it properly or not but just to be sure, to which 'table' should I refer the ALLSELECTED function? I tried with my calendar table and didn't work.

Thanks in advance!

Regards,

[v-yuta-msft]

@Anonymous ,

This based on your slicer. If you build slicer based on date column in the fact table, then the expression should be ALLSELECTED(Fact_Table). 

Community Support Team _ Jimmy Tao

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

[Anonymous]

@v-yuta-msft , unfortunately it's not working. 

So, the measure is working but it's not doing what it's supposed to. 

Slicer is based on a column in my CALENDAR table which is, of course, connected to my fact table.

First 3 colums are my current measures, last one the one using your solution 

PREV. WK FCST =

CALCULATE ( [FCST], FILTER ( ALLSELECTED ( 'CALENDAR' ), DATEADD('CALENDAR'[Date], -7,DAY) ))

SPI WEEK	FCST	PREV. WK FCST	PREV. WK FCST V2
202003	1,868,586	1,861,980	5,628,067
201952	1,904,024	1,895,539	5,628,067
201949	1,855,457	1,836,393	5,628,067

So, I selected 3 different WKS as you can see in table above where the first 3 columns are my current measures and the last one the one you have proposed. What it's actually doing is a SUM of all the selected weeks FCST .

Instead, I would like it to show as follows:

SPI WEEK	FCST	PREV. WK FCST	PREV. WK FCST V2
202003	1,868,586	1,861,980	1,904,024
201952	1,904,024	1,895,539	1,855,457
201949	1,855,457	1,836,393	BLANK

Does it makes sense?

Thanks a lot for your time buddy.

Regards,