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.
Hello
This is my situation :
Date | Amount | Condition | Cumulative if Condition = Y |
01-01-18 | 0 | 0 | |
01-02-18 | 0 | 0 | |
01-03-18 | 0 | 0 | |
01-04-18 | 0 | 0 | |
01-05-18 | 0 | 0 | |
01-06-18 | 0 | 0 | |
01-07-18 | 0 | 0 | |
01-08-18 | 0 | 0 | |
01-09-18 | 0 | 0 | |
01-10-18 | 57896 | Y | 57896 |
01-11-18 | 0 | 57896 | |
01-12-18 | 0 | 57896 | |
01-01-19 | 0 | 57896 | |
01-02-19 | 0 | 57896 | |
01-03-19 | 0 | 57896 | |
01-04-19 | 0 | 57896 | |
01-05-19 | 0 | 57896 | |
01-06-19 | 115000 | Y | 172896 |
01-07-19 | 0 | 172896 | |
01-08-19 | 25000 | N | 172896 |
01-09-19 | 0 | 172896 | |
01-10-19 | 0 | 172896 | |
01-11-19 | 38125 | Y | 211021 |
01-12-19 | 56000 | N | 211021 |
01-01-20 | 211021 |
The 3 first columns are the data in. The last column is the result i'd like to have : a cumulative sum based on date, with condition set to Y. I tried to build a column with a CALCULATE function but i missed something...
Could you help me?
Thanks in advance and sorry for my poor English.
Michael Hendeles
Solved! Go to Solution.
Hi,
Please try to create a calculated column first:
New Amount = IF('Table'[Condition]="N",0,'Table'[Amount])
Then try this measure:
Measure = CALCULATE(SUM('Table'[New Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Hi,
Please try to create a calculated column first:
New Amount = IF('Table'[Condition]="N",0,'Table'[Amount])
Then try this measure:
Measure = CALCULATE(SUM('Table'[New Amount]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=MAX('Table'[Date])))
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Hi @mhendel ,
Try creating this measure:
Thanks for your help
Here is the result with your measure :
Date | Running Total |
01-10-18 00:00 | 57896 |
01-11-18 00:00 | 57896 |
01-12-18 00:00 | 57896 |
01-01-19 00:00 | 57896 |
01-02-19 00:00 | 57896 |
01-03-19 00:00 | 57896 |
01-04-19 00:00 | 57896 |
01-05-19 00:00 | 57896 |
01-06-19 00:00 | 172896 |
01-07-19 00:00 | 172896 |
01-08-19 00:00 | 172896 |
01-09-19 00:00 | 172896 |
01-10-19 00:00 | 172896 |
01-11-19 00:00 | 211021 |
01-12-19 00:00 | 211021 |
01-01-20 00:00 | 211021 |
Unfortunately, the rows with an amount of 0 are missing...
In fact i had the same result...
@mhendel ,
Try like
Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(date,date[date] <=max(Table[ Date])))
Or
Cumm Sales = CALCULATE(SUMX(Table,if(Table[Condition]="Y",Table[Amount],0)),filter(All(Table),Table[date] <=max(Table[ Date])))
//old
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(all(Table),Table[date] <=max(Table[ Date])),Table[Condition]="Y")
Also, try the option with old Formula
What error you are getting
after table[Amount] it will be ; not ,
Cumm Sales = CALCULATE(SUMX(Table;if(Table[Condition]="Y";Table[Amount];0));filter(All(Table);Table[date] <=max(Table[ Date])))
@mhendel ,
Try like
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(date,date[date] <=max(Table[ Date])),Table[Condition]="Y")
Thanks for your help,
When i try your measure, i receive an error... Are you sure with your syntax?
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |