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.

     

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    ))