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.
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:
(220 = 100+40+80)
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
Go to Solution.
You could try this way as below:
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])
Create a relationship between date table and data table by date column.
Create a measure like this:
SUM ( 'Table'[Value] ),
ALL ( 'Table' ),
'Table'[Date] <= MAX ( 'Date'[end of the month] )
&& 'Table'[Status] = FALSE (),
'Table'[Date] > MAX ( 'Date'[end of the month] )
&& 'Table'[Status] = TRUE ()
here is pbix file, please try it.
View solution in original post
Yes, it is because of the Filter ALL.
You could try to use ALLSELECTED instead of ALL
If you still have problem, just please share a simple sample pbix file and your expected out.
Perfect and Thank You so much.
SUM ( 'Table' [Value 1] );
ALL ( 'Table' );
AND('Table' [Data] <= MAX ( 'Time'[Fine Mese] );'Table' [Status] = FALSE ()
SUM ( 'Table' [Value 2] );
ALL ( 'Table' );
AND('Table' [Data] > MAX ( 'Time'[Fine Mese] ); 'Table' [Status] = TRUE ()
Thank you so much @v-lili6-msft ,
now, with ALLSELECTED, it should be perfect.
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!