Retrieve last day's data

[David-INTEX]

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 · Accepted solution]

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]

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 !

[v-zhangti]

Hi, @David-INTEX 

Can you provide a simple example file? Sensitive data can be removed in advance.

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

What kind of expected results do you expect? You can also show it with pictures.

Best Regards

[David-INTEX]

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]

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

[David-INTEX]

Thanks for your reply.
I'll try it and I give you update

[Whitewater100]

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]

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

[Whitewater100]

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