Forum Discussion
Accumulating sales per day
Hi gang,
I have an issue where I can't figure out how to accumulate properly.
Using following formula (based on a quickmeasure) - I can properly accumulate the "Ackumulerat" field with "Försäljning" per day.
Ackumulerat =
IF(
ISFILTERED('SalesData'[Datum]),
ERROR("Snabbmått för tidsinformation kan bara grupperas eller filtreras med den Power BI-angivna datumhierarkin eller primära datumkolumnen."),
TOTALMTD(SUM('SalesData'[Försäljning]), 'SalesData'[Datum].[Date])
)
This works fine for monthly (and smaller views).
As soon as I filter on year - it starts to act odd.
Filtering on 2020 - it accumulates from 2020-12-01 to 2020-12-31 though nothing else.
Filtering on 2021 - it accumulates nothing, maybe because we haven't reached December yet(?).
Filtering on both years - it accumulates nothing.
What am I missing here?
As a PowerBI-newbie I'm suspecting it has to do with the "isfiltered" statement in the measure though I have no idea what to look for, help would be greatly appreciated!
Hi Anonymous ,
I did not reproduce your problem.
Maybe you can try to create a calendar table and modify the formula as shown below.
And replace all the date fields used in visuals with the date field of the calendar table.
Ackumulerat = IF( ISFILTERED('SalesData'[Datum]), ERROR("Snabbmått för tidsinformation kan bara grupperas eller filtreras med den Power BI-angivna datumhierarkin eller primära datumkolumnen."), TOTALMTD( SUM('SalesData'[Försäljning]), 'Calendar table'[Date] ) )If it's useless, please provide a sample .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
v-lionel-msftCommunity Support
Hi Anonymous ,
I did not reproduce your problem.
Maybe you can try to create a calendar table and modify the formula as shown below.
And replace all the date fields used in visuals with the date field of the calendar table.
Ackumulerat = IF( ISFILTERED('SalesData'[Datum]), ERROR("Snabbmått för tidsinformation kan bara grupperas eller filtreras med den Power BI-angivna datumhierarkin eller primära datumkolumnen."), TOTALMTD( SUM('SalesData'[Försäljning]), 'Calendar table'[Date] ) )If it's useless, please provide a sample .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-lionel-msft
Hi Anonymous ,
Has your problem been solved?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.