Comparing WTD against a Static WTD Data point

[mws5872]

I have measure that is set up to as an consistent anchor point to compare our WTD Numbers to. The challenge is right now I have it kind of hardcoded using this. 

CALCULATE(SUM(Volume[volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,08)))

this works perfectly when using WTD 3/29 ( which shows 3/23-3/29) The challenge is when we move to today for example we will start to compare WTD against just monday so  in order to make that work I would change this calculate to this above. 

CALCULATE(SUM(Volume[volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,02)))

On Tuesday it will go to 

CALCULATE(SUM(Volume[volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,03)))

This would go on until sunday and it will reset to 

CALCULATE(SUM(Volume[volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,08)))

then loop it over. is there a way to make this happen automatically based on what date is selected  from date table ?

[mws5872 · Accepted solution]

Hi all here is the arrived at solution. I wanted to share in case helpful to others! 

Static Comparison = SWITCH(VALUES('Date'[Weekday]),2,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,08))),

3,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,02))),

4,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,03))),

5,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,04))),

6,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,05))),

7,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,06))),

1,CALCULATE(SUM(Volume[Volume]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,07))))

[v-easonf-msft]

Hi, @mws5872 

Not very clear. Can you explain bit more.
Sample data for tables  and expected result  will make it easier for us to understand and  solve your problem.

Best Regards,
Community Support Team _ Eason

[mws5872]

Hopefully this makes it clearer. I created a date table and the measures attached in a file.

https://drive.google.com/open?id=1CL7GApXM7jGKrpJBrLUs_AJXU9hm-k3w

We have 3 measures:

WTD shows M-Sun WTD numbers for the date selection

PW WTD Shows M-Sun Prior week to date numbers for the date selection

Anchor Point shows the volume for 3-2 to 3-8 ( it is hard coded) the challenge is depending on the date selection I want it to be dynamic. If you select a :

Monday --> 

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,08)))

Tuesday --> 

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,02)))

Wednesday -->

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,03)))

Thursday --> 

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,04)))

Friday --> 

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,05)))

Saturday --> 

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,06)))

Sunday -->

CALCULATE(SUM(Data[Amount]),DATESBETWEEN('Date'[Date],Date(2020,03,02),date(2020,03,07)))

and repeat.

I hope this makes more sense.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490