The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I want an cumulative count, by month, of new matters (new rows in my data source) grouped also by "project type"
I was able to write a measure for this - this was a huge challenge to begin with.
tableTestMr = CALCULATE(
COUNTA('LPM Tracker'[index]),
FILTER(ALL('LPM Tracker'),
'LPM Tracker'[Created]<=MAX('Date'[Date]) &&
MAX('Date'[Date])<=TODAY() &&
'LPM Tracker'[Project Type]=MAX('LPM Tracker'[Project Type])
)
)
@Anonymous
First, on your measure, as per Marco russo and other DAX expert's advice, i would suggest using variables for Current Year or CrDayOfYear and use that to be equal to your Dates[Date] value.
The performance will be better as well as readability of your DAX measure.
Check your FILTER once again. The order in which your Dates and LPMTracker is being filtered can cause your data to get rolled into a different month.
Is your Fiscal period from February to January?
"Now, when I try to display the data, it shows nothing for any period where new matters were not added for aparticular project type. I want the value to repeat instead of returning a blank - the ribbon chart in particular looks terrible when individual ribbons just drop off. I've spent hours trying to figure this out and I'm starting to suspect that it's impossible"
-- I would recomend you to add a '+0' at the end of your cumulative count measure. this will add a zero or 0 value whenever it does not have value.
Something like this:-
tableTestMr = CALCULATE(
COUNTA('LPM Tracker'[index]),
FILTER(ALL('LPM Tracker'),
'LPM Tracker'[Created]<=MAX('Date'[Date]) &&
MAX('Date'[Date])<=TODAY() && -- Check and change the order between this filter and the previous one.
-- Sometimes the && filter does not work as well as it is supposed to due to lack of parantheses.
'LPM Tracker'[Project Type]=MAX('LPM Tracker'[Project Type])
)
) +0 -- Add whatever value you want, for the sake of testing i have added +0
The +0 will make your graphs especially the running totals look much much better.
"Edit: also, there should definitely be a February 2020 bucket for some of the data - why would it get rolled into January?"
-- Probably caused by the way your calculate () is being executed. MAX(Dates[Date]) may not be equal to TODAY(). Just try to simplify it with variables and use the calculate in the Return Clause with the filters pointing directly to the scalar variable value.