How to show default values for missing periods

Question

Hi All,&nbsp;I have a data with period (Year-Month). I would like to show default values&nbsp; "0" for mising periods&nbsp;I have 2 Groups like (A and B) with respective values like below&nbsp;Let's sayPeriod&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group&nbsp; &nbsp; &nbsp;Date (mm/dd/yyyy)2019-01&nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/01/2019 00:00:00:002019-02&nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 02/01/2019 00:00:00:002019-03&nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03/01/2019&nbsp;00:00:00:002019-04&nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2019 00:00:00:002019-05&nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05/01/2019&nbsp;00:00:00:002019-06&nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06/01/2019 00:00:00:00&nbsp;Group i have taken as a Slicer&nbsp;When i select Group (A) in slicer&nbsp; it is givingPeriod&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group&nbsp; &nbsp; &nbsp;Date (mm/dd/yyyy)2019-01&nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/01/2019 00:00:00:002019-02&nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2019 00:00:00:002019-03&nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03/01/2019&nbsp;00:00:00:002019-04&nbsp; &nbsp; &nbsp; &nbsp;6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2019 00:00:00:002019-05&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05/01/2019&nbsp;00:00:00:002019-06&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06/01/2019 00:00:00:00&nbsp;When i select Group (B) in slicer it is givingPeriod&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group&nbsp; &nbsp; &nbsp;Date (mm/dd/yyyy)2019-01&nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/01/2019 00:00:00:002019-02&nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2019 00:00:00:002019-05&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05/01/2019&nbsp;00:00:00:002019-06&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06/01/2019 00:00:00:00&nbsp;it is missing 2019-03, 2019-04, It means there is no values for those period for "B". Here i want to display those missing periods also with "0" value&nbsp;along with the exisitng periods. .&nbsp;&nbsp;Output need like thisPeriod&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Group&nbsp; &nbsp; &nbsp;Date (mm/dd/yyyy)2019-01&nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;01/01/2019 00:00:00:002019-02&nbsp; &nbsp; &nbsp; &nbsp;5&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;02/01/2019 00:00:00:002019-03&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 03/01/2019&nbsp;00:00:00:002019-04&nbsp; &nbsp; &nbsp; &nbsp;0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;04/01/2019 00:00:00:002019-05&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 05/01/2019&nbsp;00:00:00:002019-06&nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;06/01/2019 00:00:00:00&nbsp;I created calender table in my dataset and gave relationship between both the date columns.But when i'm giving relationship it is not showing the missing periods in the visual.&nbsp;Can anybody let me know how to achive this.Thanks in advance

az38 · Answer

Hi&nbsp;harib&nbsp;
did you try to set "Show items with no data" in visual settings?

allisonkennedy · Answer

harib&nbsp; 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).&nbsp;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.&nbsp;DimPeriod = VALUES('Table'[Period])&nbsp;Then relate the DimPeriod table to the datasources table as in my image.&nbsp;Use the DimPeriod table for the Period column and get the values from your source data table. And finally as&nbsp;az38&nbsp; suggested, 'show items with no data' to see missing periods.&nbsp;&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;https://sites.google.com/site/allisonkennedycv

allisonkennedy · Answer

You could try a measure in that case.&nbsp;&nbsp;Total value = 0 + sum (value)Or might need to be sumx depending on how you use it.&nbsp;

allisonkennedy · Answer

harib&nbsp; 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?&nbsp;Just a bit more information needed so we can help you.&nbsp;&nbsp;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.&nbsp;&nbsp;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.&nbsp;https://sites.google.com/site/allisonkennedycv

harib · Answer

AllisonKennedy&nbsp;&nbsp;I have provided sample data . Please check once.&nbsp;&nbsp;sample Data file :&nbsp;https://drive.google.com/file/d/1brZTsnfqx0mTryREYkTrb5PreaC5mO8a/view?usp=sharing&nbsp;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.&nbsp;I hope it's clear to you.&nbsp;

Forum Discussion

How to show default values for missing periods

7 Replies

Related content

missing data

missing data

Generating Missing Data

Inferring Missing Columns and Imputing Missing Data

Copilot: Missing time period — should it ask or clarify?