Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good day! Tell me how you can summarize the data for a month, based on the last date of his application?
I have the most recent date, for example 10/15/21, how can I sum all 10 months and compare with the previous month where the user was reported? for example - it was 10/15/21 and 08/02/2021, how to force the comparison of the amount for 10 months with the amount for 8 months?
Solved! Go to Solution.
@DeEviloN , Do you YTD from last date ?
example
YTD=
var _max = maxx(allselected('Table'),'Table'[date])
var _min = date(_max),1,1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
Assumed you have date table or filer table[Date]
or
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >=_min && 'Table'[Date] <= _max) )
or
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
Hi @DeEviloN ,
Since your data is not regular, you cannot use dax sort to get to the last one. You need to add indexes via Power Query as an aid.
Step1, add index,then close and apply:
Step 2,try the measure ,to get last ereryday value:
lastsaleeverydate =
IF (
CALCULATE (
MAX ( 'Table'[Index] ),
FILTER (
ALL ( 'Table' ),
'Table'[Users] = MAX ( 'Table'[Users] )
&& 'Table'[Data] = MAX ( 'Table'[Data] )
)
)
= MAX ( 'Table'[Index] ),
MAX ( 'Table'[Sales] ),
0
)
Output:
Then use the sumx to compare value:
sumx = SUMX(FILTER(ALL('Table'),'Table'[Users]=MAX('Table'[Users])&&'Table'[Data]<=MAX('Table'[Data])),'Table'[lastsaleeverydate])
You will get the sum value:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
last date
Users | Data | Sales |
User1 | 1.8 | 270,00 |
User1 | 2.8 | 270,00 |
User1 | 2.8 | 4560,00 |
User1 | 2.8 | 540,00 |
User1 | 2.8 | 540,00 |
User1 | 2.8 | 1090,00 |
User1 | 2.8 | 5910,00 |
User1 | 10.10 | 660,00 |
User1 | 10.10 | 1080,00 |
User1 | 12.10 | 1260,00 |
User1 | 13.10 | 11220,00 |
User1 | 14.10 | 30,00 |
User1 | 15.10 | 60,00 |
User1 | 15.10 | 160,00 |
User1 | 15.10 | 180,00 |
Users | Data | Sales |
User1 | 1.8 | 270,00 |
User1 | 2.8 | 270,00 |
User1 | 2.8 | 4560,00 |
User1 | 2.8 | 540,00 |
User1 | 2.8 | 540,00 |
User1 | 2.8 | 1090,00 |
User1 | 2.8 | 5910,00 |
User1 | 10.10 | 660,00 |
User1 | 10.10 | 1080,00 |
User1 | 12.10 | 1260,00 |
User1 | 13.10 | 11220,00 |
User1 | 14.10 | 30,00 |
User1 | 15.10 | 60,00 |
User1 | 15.10 | 160,00 |
User1 | 15.10 | 180,00 |
Sorry, corrected the text
Good day! Tell me how you can summarize the data for a month, based on the last date of his application?
I have the most recent date, for example 10/15/21, how to sum this 10 month and compare with the previous month where the user was reported? for example - it was 10/15/21 and 08/02/2021, how to force the comparison of the amount for 10 months with the amount for 8 months?
@DeEviloN , Do you YTD from last date ?
example
YTD=
var _max = maxx(allselected('Table'),'Table'[date])
var _min = date(_max),1,1)
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
Assumed you have date table or filer table[Date]
or
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >=_min && 'Table'[Date] <= _max) )
or
CALCULATE(sum('Table'[Qty]), FILTER(('Date'),'Date'[Date] >=_min && 'Date'[Date] <= _max) )
It still doesn't work, I wanted to compare based on the last month of delivery of the client's products and the previous month of his delivery to determine its category
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |