Forum Discussion

David-INTEX's avatar
David-INTEX
Regular Visitor
4 years ago
Solved

Retrieve last day's data

  Hello to all, In a report I would like to display the figures for Friday on Monday (knowing that we have no figures in the weekend). Let's imagine we are on 23/05/2022, I want to display on the ...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, David-INTEX 

     

    You can try the following methods.

    Measure:

    PreTurnover = 
Var PrevDate=MAXX(FILTER(ALL('Date'[Date]),'Date'[Date]<SELECTEDVALUE('Date'[Date])),'Date'[Date])
Var PreTurnover=CALCULATE(SUM('Date'[Value]),FILTER(ALL('Date'),[Date]=PrevDate))
Return
PreTurnover

     

    Today = CALCULATE([PreTurnover],FILTER(ALL('Date'),[Date]=TODAY()))

    Is this the output you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

     

David-INTEX's avatar
David-INTEX
Regular Visitor
4 years ago

Hello,

Sorry for the delay in responding.

The expected result is that on Monday morning, I have the Friday figures displayed every time. Currently, since in my Dates table, I have the weekend days, my result is impossible with the proposed solutions.
Do you have a solution to remove the weekend days in the Dates table in DAX?

 

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
4 years ago

Hi:

You can add 2 calc columns to your date table. Assume Date Table is named "Dates".

 

WeekDay = WEEKDAY(Dates[Date])   // this will give you day of week 1-7

WorkDay = Dates[WeekDay] <> 1 && Dates[WeekDay] <>7 // this will give TRUE for M-F and False for Weekends. 
You can filter so you set this to TRUE and weekends won't be involved.
 
You can make just one calc col in Dates if you want to do:
WorkDay Flag = 
var dayofweek =  WEEKDAY(Dates[Date])
result
dayofweek <> 1 && dayofweek <>7
 
I hope this helps