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.
Hi,
I need your help to solve my problem
I have a table containing dates, values and status.
The goal is to have the trend by month. The sum must be paid at the end of each month. That is all the values from the beginning until the end of the month.
I need to sum all the value that have status "FALSE" before the end of the month and the value that have status "TRUE" after the same date.
For example:
date Value Status
22/01/2019 100 False
25/01/2019 40 False
15/02/2019 60 False
24/02/2019 80 True
i would like to have the result:
31/01/2019 220
(220 = 100+40+80)
28/02/2019 200
(200= 100+40+60)
I sum 80 in January because he is true after 31/01/2019, and i don't sum it in February because in 28/02/2019 it is TRUE.
I hope to be able to explain myself despite my English
Thanks
Giovanni
Solved! Go to Solution.
hi, @nannimora
You could try this way as below:
Step1:
Add a date table and add an end of month column in it
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
end of the month = ENDOFMONTH('Date'[Date])
Step2:
Create a relationship between date table and data table by date column.
Step3:
Create a measure like this:
Measure = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), OR ( 'Table'[Date] <= MAX ( 'Date'[end of the month] ) && 'Table'[Status] = FALSE (), 'Table'[Date] > MAX ( 'Date'[end of the month] ) && 'Table'[Status] = TRUE () ) ) )
Result:
here is pbix file, please try it.
Best Regards,
Lin
hi, @nannimora
Yes, it is because of the Filter ALL.
You could try to use ALLSELECTED instead of ALL
https://docs.microsoft.com/en-us/dax/all-function-dax
https://docs.microsoft.com/en-us/dax/allselected-function-dax
If you still have problem, just please share a simple sample pbix file and your expected out.
Best Regards,
Lin
hi, @nannimora
You could try this way as below:
Step1:
Add a date table and add an end of month column in it
Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
end of the month = ENDOFMONTH('Date'[Date])
Step2:
Create a relationship between date table and data table by date column.
Step3:
Create a measure like this:
Measure = CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), OR ( 'Table'[Date] <= MAX ( 'Date'[end of the month] ) && 'Table'[Status] = FALSE (), 'Table'[Date] > MAX ( 'Date'[end of the month] ) && 'Table'[Status] = TRUE () ) ) )
Result:
here is pbix file, please try it.
Best Regards,
Lin
Perfect and Thank You so much.
Trend = CALCULATE ( SUM ( 'Table' [Value 1] ); FILTER ( ALL ( 'Table' ); AND('Table' [Data] <= MAX ( 'Time'[Fine Mese] );'Table' [Status] = FALSE () ) ) ) + CALCULATE ( SUM ( 'Table' [Value 2] ); FILTER ( ALL ( 'Table' ); AND('Table' [Data] > MAX ( 'Time'[Fine Mese] ); 'Table' [Status] = TRUE () ) ) )
hi, @nannimora
Yes, it is because of the Filter ALL.
You could try to use ALLSELECTED instead of ALL
https://docs.microsoft.com/en-us/dax/all-function-dax
https://docs.microsoft.com/en-us/dax/allselected-function-dax
If you still have problem, just please share a simple sample pbix file and your expected out.
Best Regards,
Lin
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |