How to show default values for missing periods

[harib]

Hi All,

I have a data with period (Year-Month). I would like to show default values  "0" for mising periods

I have 2 Groups like (A and B) with respective values like below

Let's say

Period          Group     Date (mm/dd/yyyy)

2019-01       4              01/01/2019 00:00:00:00

2019-02       10              02/01/2019 00:00:00:00

2019-03       5              03/01/2019 00:00:00:00

2019-04       6               04/01/2019 00:00:00:00

2019-05       6              05/01/2019 00:00:00:00

2019-06       6               06/01/2019 00:00:00:00

Group i have taken as a Slicer

When i select Group (A) in slicer  it is giving

Period          Group     Date (mm/dd/yyyy)

2019-01       2              01/01/2019 00:00:00:00

2019-02       5              02/01/2019 00:00:00:00

2019-03       5             03/01/2019 00:00:00:00

2019-04       6               04/01/2019 00:00:00:00

2019-05       3              05/01/2019 00:00:00:00

2019-06       3               06/01/2019 00:00:00:00

When i select Group (B) in slicer it is giving

Period          Group     Date (mm/dd/yyyy)

2019-01       2             01/01/2019 00:00:00:00

2019-02       5              02/01/2019 00:00:00:00

2019-05       3              05/01/2019 00:00:00:00

2019-06       3               06/01/2019 00:00:00:00

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 along with the exisitng periods. . 

Output need like this

Period          Group     Date (mm/dd/yyyy)

2019-01       2              01/01/2019 00:00:00:00

2019-02       5              02/01/2019 00:00:00:00

2019-03       0              03/01/2019 00:00:00:00

2019-04       0               04/01/2019 00:00:00:00

2019-05       3              05/01/2019 00:00:00:00

2019-06       3               06/01/2019 00:00:00:00

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.

Can anybody let me know how to achive this.

Thanks in advance

[az38]

Hi @harib 

did you try to set "Show items with no data" in visual settings?

[AllisonKennedy]

@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. 

https://sites.google.com/site/allisonkennedycv

[harib]

@AllisonKennedy 

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. 

[AllisonKennedy]

@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. 

https://sites.google.com/site/allisonkennedycv

[harib]

@AllisonKennedy 

It's almost met my requirement, however i would like to display "0" values for that periods instead of showing blank .

[AllisonKennedy]

You could try a measure in that case. 

Total value = 0 + sum (value)

Or might need to be sumx depending on how you use it. 

[harib]

@AllisonKennedy 

Solved my Problem. Thanks a lot. 😊