Forum Discussion
How to show default values for missing periods
harib can you please clarify your data. Where is A and B in the sample rows and how do you calculate the Group column value? Is that a measure to help you combine the two groups?
Just a bit more information needed so we can help you.
Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
I have provided sample data . Please check once.
sample Data file : https://drive.google.com/file/d/1brZTsnfqx0mTryREYkTrb5PreaC5mO8a/view?usp=sharing
If we filter "B" from the group its will not show 2019-03, 2019-04, 2019-06 values and periods, because in the data it self period was not there for that transaction. So we need to show missing periods with dummy values along with ohter periods.
I hope it's clear to you.
harib Your issue is that everything is in one table, so when you filter for group B, you lose the Periods not related to B. You need to create a related data model with dimension table for period (each period exactly once).
You can use DAX new Table to do this quickly (what I did for testing) but I prefer to have the dimension tables loaded into the power query.
DimPeriod = VALUES('Table'[Period])Then relate the DimPeriod table to the datasources table as in my image.Use the DimPeriod table for the Period column and get the values from your source data table.And finally as az38 suggested, 'show items with no data' to see missing periods.Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
It's almost met my requirement, however i would like to display "0" values for that periods instead of showing blank .
