Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |