Forum Discussion
Helper IIComparing WTD against a Static WTD Data point
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.
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))))
4 Replies
- Greg_Deckler
Community Champion
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
- v-easonf-msft
Community Support
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
mws5872Hopefully 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.
- mws5872
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))))
