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.
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))))
Solved! Go to Solution.
Hi,
Does this measure work?
=if(isfiltered('All BPOC Reports'[PROJEKTNR]),[billing vpm],sumx(VALUES('All BPOC Reports'[PROJEKTNR]),[billing vpm]))
Hi,
Does this measure work?
=if(isfiltered('All BPOC Reports'[PROJEKTNR]),[billing vpm],sumx(VALUES('All BPOC Reports'[PROJEKTNR]),[billing vpm]))
You are welcome.
@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])
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |