Forum Discussion
Another Sumx ....
Hi
I have a dataset that contains billing for each project for each month. What I am presenting in the matrix is the billing delta versus the prior month for each project (line item).
For the line items, the values are correct. However, the total is showing the delta month compared to month instead of summing the line items. I know it has to be solved with sumx/summarize, but I am not able to figure this out.
My formula is:
Billing vPM =
//Original Formula
Calculate(sum('All BPOC Reports'[Actual Billing])-CALCULATE(SUM('All BPOC
Reports'[Actual Billing]),PARALLELPERIOD('Calendar'[Date],-1,MONTH)))
//My Sumx Approach
if(isfiltered('All BPOC Reports'[PROJEKTNR]),
calculate(sum('All BPOC Reports'[Actual Billing]))-CALCULATE(SUM('All BPOC Reports'[Actual Billing]),PARALLELPERIOD('Calendar'[Date],-1,MONTH)),
sumx('All BPOC Reports',
calculate(sum('All BPOC Reports'[Actual Billing]))-CALCULATE(SUM('All BPOC Reports'[Actual Billing]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))))
Hi,
Does this measure work?
=if(isfiltered('All BPOC Reports'[PROJEKTNR]),[billing vpm],sumx(VALUES('All BPOC Reports'[PROJEKTNR]),[billing vpm]))
4 Replies
Anonymous , Try datesmtd or previousmonth with a date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
Appreciate your Kudos.diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])Hi,
Does this measure work?
=if(isfiltered('All BPOC Reports'[PROJEKTNR]),[billing vpm],sumx(VALUES('All BPOC Reports'[PROJEKTNR]),[billing vpm]))it works Ashish_Mathur - thank you so much! 🙂
You are welcome.
