Forum Discussion
Rolling YTD measure using fields with category
Hi there.
I've used this (9) Power BI Show Year-to-Date (YTD) Across Multiple Years - YouTube by Avi Singh to build a measure which calculates the Rolling YTD sum of kg porduced.
The formula is:
qty_kg_ROLYTD = VAR MaxMonthYTD = [This Month for YTD]
RETURN calculate(sum(SAPProdConfirmation[qty_kg]), DateTable[month]=MaxMonthYTD)
where "This Month for YTD is":
This Month for YTD = calculate(MAX(DateTable[month]), ALL(DateTable),(DateTable[MonthOffset]=0))
My dataset can manage that but I have a category for each row of quantity, which says if the quantity declared is scrap or good.
For example:
| qty_kg | quality_status |
| 1000 | scrap |
| 2000 | scrap |
| 3000 | good |
| 4000 | good |
| 5000 | good |
The measure I've built does not fit with the quality status assignment, as you can see:
Actually I'm not surprise, but I need to fix this without creating a measure for each quality status value, I want to manage it in a single measure.
Do you have any suggestion? Thanks.
I'd expect your measure to work fine as long as the quality_status column is also from SAPProdConfirmation or a table that filters that fact table via a relationship.
Can you confirm the quality is not coming from a disconnected table or a table that doesn't filter SAPProdConfirmation via a relationship?
2 Replies
- AlexisOlson
Super User
I'd expect your measure to work fine as long as the quality_status column is also from SAPProdConfirmation or a table that filters that fact table via a relationship.
Can you confirm the quality is not coming from a disconnected table or a table that doesn't filter SAPProdConfirmation via a relationship?
- andreazambon
Helper V
Actually, it is like you supposed and it works fine.
I don't know why, probably I miss something in the original formula... nevermind. Thanks for answer.