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.
I have a situation where I have to sum the remaining values of a certain model.
My Table in the data model looks like this:
ID Value Date Amount Creation Date
1 01/01/2018 1 000 01/01/2018
2 01/02/2018 1 000 01/01/2018
3 01/03/2018 1 000 01/01/2018
4 01/04/2018 1 000 01/01/2018
5 01/05/2018 1 000 01/05/2018
6 01/06/2018 1 000 01/01/2018
7 01/07/2018 0
The goal is to have a measure Remaining Value that would have this output:
Date Remaining Value
Jan 5 000
Fev 4 000
Mar 3 000
Abr 2 000
Mai 2 000
Jun 1 000
Jul 0
Value Date is related to my calendar table.
In Jan, the logic is Sum Value Date >= 01/01/2018 with Creation Date >= 01/01/2018 and Creation Date<01/05/2018...
I tried the measure like this but is not working:
Remaining Value:=calculate( sum[Amount]; filter( all('Calendar'[Date]); 'Calendar'[Date]>max('Calendar'[Date]) && 'Calendar'[Date]<=max(Table[Creation Date] ) )
Solved! Go to Solution.
Hi @excelso21,
If I understand you correctly, the formula below should work in your scenario.
Measure = VAR firstDateOfMonth = FIRSTDATE ( 'Calendar'[Date] ) VAR maxCreationDate = MAX ( Table1[Creation Date] ) RETURN CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, Table1[Creation Date] >= firstDateOfMonth && Table1[Creation Date] < maxCreationDate ) )
Regards
Hi @excelso21,
If I understand you correctly, the formula below should work in your scenario.
Measure = VAR firstDateOfMonth = FIRSTDATE ( 'Calendar'[Date] ) VAR maxCreationDate = MAX ( Table1[Creation Date] ) RETURN CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, Table1[Creation Date] >= firstDateOfMonth && Table1[Creation Date] < maxCreationDate ) )
Regards
Thanks. It worked.
Alternatevily I figured out also this measure:
Measure:=-calculate( sum(Table1[Amount] ); filter( all('Calendar'); 'Calendar'[Date]>max('Calendar'[Date]) ); filter( all(Table1[Creation Date]); Table1[Creation Date]<=max('Calendar'[Date]) ) )
Hello, it is my first post so sorry if I don't use this Community standards.
I have a situation where I have to sum the remaining values of a certain model.
My Table in the data model looks like this:
ID Value Date Amount Creation Date
1 01/01/2018 1 000 01/01/2018
2 01/02/2018 1 000 01/01/2018
3 01/03/2018 1 000 01/01/2018
4 01/04/2018 1 000 01/01/2018
5 01/05/2018 1 000 01/05/2018
6 01/06/2018 1 000 01/01/2018
7 01/07/2018 0
The goal is to have a measure Remaining Value that would have this output:
Date Remaining Value
Jan 5 000
Fev 4 000
Mar 3 000
Abr 2 000
Mai 2 000
Jun 1 000
Jul 0
Value Date is related to my calendar table.
In Jan, the logic is Sum Value Date >= 01/01/2018 with Creation Date >= 01/01/2018 and Creation Date<01/05/2018...
I tried the measure like this but is not working:
Remaining Value:=calculate(
sum[Amount];
filter(
all('Calendar'[Date]);
'Calendar'[Date]>max('Calendar'[Date]) &&
'Calendar'[Date]<=max(Table[Creation Date]
)
)
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |