Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
HI,
I have a column with dates and values. The date column is not including some days, so I need to create a column with the value of the previous date like this one (Also the table has an ID column with different ID's
Date | ID | Value | Result |
01/01/2020 | ABC | 100 | - |
02/01/2020 | ABC | 200 | 100 |
5/01/2020 | ABC | 150 | 200 |
06/01/2020 | ABC | 110 | 150 |
08/01/2020 | ABC | 300 | 110 |
Thanks for the help
Agus
Solved! Go to Solution.
Hi @Anonymous ,
Check the formula below.
Column =
var pre_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]=pre_date))
Result would be shown as below.
For earlier() function please check the below document.
https://docs.microsoft.com/en-us/dax/earlier-function-dax
Best Regards,
Jay
@Anonymous , Try a new column
New column =
var _min = maxx(filter(Table, [ID] = earlier([ID]) && [Date] <earlier([Date])),[Date])
return
maxx(filter(Table, [ID] = earlier([ID]) && [Date] =_min),[Result])
I dont understand two things of your DAX:
- Earlier[ID] ?
- [Result]?
Thanks
Hi @Anonymous ,
Check the formula below.
Column =
var pre_date = CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[ID]),'Table'[Date]<EARLIER('Table'[Date])))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Date]=pre_date))
Result would be shown as below.
For earlier() function please check the below document.
https://docs.microsoft.com/en-us/dax/earlier-function-dax
Best Regards,
Jay
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |