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.
Hello everyone,
I found this DAX code to calculate a yearly running total :
Qty Invoiced running total in Month =
CALCULATE(
SUM('Revenue'[Qty Invoiced]),
FILTER(
ALLSELECTED('Revenue'),
'Revenue'[Year] = MAX ( 'Revenue'[Year] ) &&
'Revenue'[Month] <= MAX ( 'Revenue'[Month] )
)
)
I am using the Area chart with the Year in legend to show the yearly difference however my running total stop on the month when I don't have data, to continue on the next one.
Any clue how I could enhance this measure ?
Solved! Go to Solution.
@Anonymous Use date table that will make sure you have data for the month you do not have data
Qty Invoiced running total in Month =
CALCULATE(
SUM('Revenue'[Qty Invoiced]),
FILTER(
ALLSELECTED('Date'),
'Date'[Year] = MAX ( 'Date'[Year] ) &&
'Date'[Month] <= MAX ( 'Date'[Month] )
)
)
to means this seem like YTD only
YTD Sales = CALCULATE(SUM(Revenue[Qty Invoiced]),DATESYTD('Date'[Date],"12/31"))
for runnign total
Qty Invoiced running total in Month =
CALCULATE(
SUM('Revenue'[Qty Invoiced]),
FILTER(
ALLSELECTED('Date'),
'Date'[DAte] <= MAX ( 'Date'[Date] )
)
)
@Anonymous Use date table that will make sure you have data for the month you do not have data
Qty Invoiced running total in Month =
CALCULATE(
SUM('Revenue'[Qty Invoiced]),
FILTER(
ALLSELECTED('Date'),
'Date'[Year] = MAX ( 'Date'[Year] ) &&
'Date'[Month] <= MAX ( 'Date'[Month] )
)
)
to means this seem like YTD only
YTD Sales = CALCULATE(SUM(Revenue[Qty Invoiced]),DATESYTD('Date'[Date],"12/31"))
for runnign total
Qty Invoiced running total in Month =
CALCULATE(
SUM('Revenue'[Qty Invoiced]),
FILTER(
ALLSELECTED('Date'),
'Date'[DAte] <= MAX ( 'Date'[Date] )
)
)
Hi Amit,
Thanks for the inputs.
I created a date table following this procedure , however whether I write one of the two codes you mentionned my output is incorrect:
Am I missing something ?
@Anonymous , hope axis is also coming from date table also check formula, seems like only allselected and = max is mising
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |