topic Re: Retrieve last day's data in DAX Commands and Tips

Retrieve last day's data

David-INTEX — Thu, 02 Jun 2022 14:31:18 GMT

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.

Re: Retrieve last day's data

Whitewater100 — Thu, 02 Jun 2022 18:44:18 GMT

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.

Re: Retrieve last day's data

David-INTEX — Fri, 03 Jun 2022 09:21:07 GMT

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

Re: Retrieve last day's data

David-INTEX — Mon, 06 Jun 2022 06:53:24 GMT

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

Re: Retrieve last day's data

Whitewater100 — Mon, 06 Jun 2022 11:52:08 GMT

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

Re: Retrieve last day's data

v-zhangti — Tue, 07 Jun 2022 02:06:43 GMT

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.

Re: Retrieve last day's data

David-INTEX — Tue, 07 Jun 2022 07:34:08 GMT

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 !

Re: Retrieve last day's data

v-zhangti — Tue, 07 Jun 2022 08:24:42 GMT

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

Re: Retrieve last day's data

David-INTEX — Wed, 15 Jun 2022 09:38:05 GMT

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?

Re: Retrieve last day's data

Whitewater100 — Wed, 15 Jun 2022 14:37:28 GMT

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