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 screen the number of files of 20/05/2022.

 

I already have a DAX formula:
Turnover of the previous day =
VAR cd = MAX ( 'Date'[Date] )
VAR previousdate =
CALCULATE (
MAX ( 'Date'[Date] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] < cd )
)
RETURN
CALCULATE (
SUM ( 'Order'[Amount] ),
FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = previousdate )
)

 

Unfortunately I can't do what I want at the moment.

Thank you for your help.

 

 

  • 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.

     

9 Replies

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

    Hi:

    You can try these measures. Please first add Date Table(named Dates) and mark as Date Table and form relationship to your data table on DateCreation). I will paste Date Table code below. 

    MODELING>NEW TABLE> see below

    Add two new measures after Date Table is connected properly.

    Total Amt = SUM(Table[Nombre..Dossier])

    Previous Date Amt =
    VAR FirstvisDate =
    MIN( Table[DateCreation])
    VAR Result =
    CALCULATE (LASTNONBLANKVALUE (
    DISTINCT ( Table[DateCreation] ),
    [Total Amt]
    ),
    REMOVEFILTERS ( 'Dates' ),
    Table[DateCreation] < FirstVisDate
    )
    RETURN
    Result

     

    It will be great if you add a Date Table and form relationship with your data table. MODELING>NEW TABLE

    then this DAX code:

    Dates = ADDCOLUMNS ( CALENDAR (FIRSTDATE(Sheet1[ Date]), TODAY()), "year", YEAR ( [Date] ), "MonthNumber", FORMAT ( [Date], "MM" ), "year-month", FORMAT ( [Date], "YYYY-MM" ), "month-year", FORMAT ( [Date], "MM-'YY" ) )

     

    I hope this works for you.

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

      I have tried the solution but I can't get what I want. Is there any other solution?

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

        Hi:

        You can add calc columns. You can sub in your table name and date into the below. This should work for you. Good luck!

        Index = RANKX(MyTable, MyTable[Date], , ASC, Dense)

        Prev Value = CALCULATE (    SUM ( MyTable[Value] ),    FILTER (        ALLEXCEPT ( MyTable, MyTable[Attribute] ),        MyTable[Index]            = MAX ( MyTable[Index] ) - 1    ))

  • 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 !