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.

     

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
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,
Thank you for your feedback, I have tried to set this up but I can't get the figures when there are no figures the day before.
For example, I have here the case with shipped files
I can see in the data that there were some from 03/06/2022 but not before. When I use the measurements, it shows me (Empty).

However, it works very well for the days that follow. Do you have a tip to solve this?
Thanks a lot !

    • 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