Forum Discussion

mws5872's avatar
mws5872
Helper II
6 years ago
Solved

Comparing 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.    CALCULATE(SUM(Volume[volume]),DA...
Need Help
  • mws5872's avatar
    mws5872
    6 years ago

    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's avatar
v-easonf-msft
Community Support
6 years ago

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's avatar
    mws5872
    Helper II
    6 years ago

    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.