Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX CALCULATE with conditional DATEADD

Good Day.

 

I currently have two working measusres. The first shows total sales, and the second shows the total sales from the previous day:

 

MTotalSales = SUMX(AllSales,AllSales[Price]*AllSales[Quantity])
MPreviousDaySales = CALCULATE ([MTotalSales],DATEADD(AllDates[Date],-1,DAY))
 
This produces the following table:
What I'm now trying to do is create a previous sales. The only trick is for Monday. For Monday,
I need to show the sales from the previous Friday, and I thought the following would do it:
MPreviousDayOfWeekSales =
CALCULATE (
[MTotalSales],
FILTER(AllDates,IF (AllDates[DayOfWeek] = 1,
DATEADD ( AllDates[Date], -3, DAY ),
DATEADD ( AllDates[Date], -1, DAY ) )
)
)
Note that day of week "1" is Monday.
 
However, that formula just ends up showing the same day's sales:
Is there a way to build this conditional use of CALCULATE and DATEADD. Semantically:
If the day of week is not "1", then show the sales from the previous day, otherwise, show
the sales from 3 days ago?
 
Also, I know I'll have duplicates on Mondays-and-Saturdays, but that's okay because I'll filter the
table to only show weekdays.
 
Thanks in advance for your help.
  • Anonymous's avatar
    Anonymous
    5 years ago

    I ended up resolving the problem. Instead of keeping a running tabular total, I directly targeted the KPI.


    Using, variables, I was able to pull off what I needed:


    MPreviousWeekDaySales =
    VAR ThreeDaysRange = FILTER(AllDates,AllDates[Date] = TODAY() - 3)
    VAR OneDayRange = FILTER(AllDates,AllDates[Date] = TODAY() - 1)
    RETURN IF(WEEKDAY(TODAY()) = 2,SUMX(ThreeDaysRange,[MTotalSales]),SUMX(OneDayRange,[MTotalSales]))

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I ended up resolving the problem. Instead of keeping a running tabular total, I directly targeted the KPI.


    Using, variables, I was able to pull off what I needed:


    MPreviousWeekDaySales =
    VAR ThreeDaysRange = FILTER(AllDates,AllDates[Date] = TODAY() - 3)
    VAR OneDayRange = FILTER(AllDates,AllDates[Date] = TODAY() - 1)
    RETURN IF(WEEKDAY(TODAY()) = 2,SUMX(ThreeDaysRange,[MTotalSales]),SUMX(OneDayRange,[MTotalSales]))