Another Sumx ....

[Anonymous]

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))))

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

=if(isfiltered('All BPOC Reports'[PROJEKTNR]),[billing vpm],sumx(VALUES('All BPOC Reports'[PROJEKTNR]),[billing vpm]))

[Anonymous]

it works @Ashish_Mathur  - thank you so much! 🙂

[Ashish_Mathur]

You are welcome.

[amitchandak]

@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])