Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello.
I have created a measure which shows the monhtly value between two dates, a start and an end date.
Current Rev per monht = CALCULATE(
SUM('Rev contract'[Rev per Month]),
FILTER(CALCULATETABLE('Rev contract',ALL('Calendar')),
'Rev Contract'[Start]<=MIN('Calendar'[Date])&&
'Rev Contract'[End]>MIN('Calendar'[Date])
))
As you see the row calculation works but my matrix does not show a column grand total for summing up all the different periods/months.
What do I need to change to my measure?
Best regards,
Ok I managed to get annual totals by combining the SUMX funciton
Rev per Month =
SUMX(VALUES(Calendar[Month]),[Current Rev per month]
)
This works only on an annual level. Not for multiple years. I would expect the total for multiple years
@Scholes , Try like
Current Rev per monht = CALCULATE(
SUM('Rev contract'[Rev per Month]),
FILTER(CALCULATETABLE('Rev contract',ALL('Calendar')),
'Rev Contract'[Start]<=Max('Calendar'[Date])&&
'Rev Contract'[End]>=MIN('Calendar'[Date])
))
Ok I changed the formula when I add year as a column I see now a total but this total corresponds with the value of the first month, so there is no sum of the individual periods, same as the total value per year is not calculated.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |