How to show previous period value if they do not exist in current period while comparing.

[Ani26]

Hi Team,
Need your assistance on one of the issues which we am currently facing.
When we are doing a current period vs prior period comparison we want to show all the values even though few of them do not exist for the current period.
Let me explain this via some data.

Opp	Period	Value
a	Q1-23	10
b	Q1-23	10
c	Q1-23	10
d	Q1-23	10
a	Q2-23	10
b	Q2-23	10
d	Q2-23	10

I have a slicer on my page wherein when I select a period say Q2-23, then in my table it should show like below

We have measures created where we show current period and next to it the Previous Period. But when we do it via measures and select Q2-23 in the slicer we get the below output

The Opp c is missing for Previous Quarter. Though the total is correct for Previous Quarter, it does not show the Opp c and that is what is required.
We have created the below measures,

Current quarter = 
var abc = CALCULATE(Sum(Dummy[Value]),Dummy[Period] = SELECTEDVALUE(Dummy[Period])) RETURN IF(ISBLANK(abc),0,abc) 

Previous Quarter = CALCULATE(Sum(Dummy[Value]), Dummy[Period] = "Q1-23")

Currently the period is hardcoded in previous quarter measure, but even if we keep it dynamic it is giving the same issue.
Can we some how show the Opp C as well while showing the comparison?

Below is the expected output

[v-yueyunzh-msft]

Hi , @Ani26 

Accoridng to your description, you want to “show previous period value if they do not exist in current period while comparing.”.

Here are the  steps you can refer to :
(1)This is my test data:

(2)To compare , the Text type is difficulte, we can click "New Column" and enter this to crete a calculated column:

Period_Number = RIGHT([Period],2)*10+MID([Period],2,1)

(3)Then we can create two measures like this:

Current quarter = var _curent_period= (YEAR(TODAY()) -2000)*10+ QUARTER(TODAY())
return
CALCULATE( SUM('Table'[Value]) , 'Table'[Period_Number] = _curent_period)+0

Previous Quarter = var _Previous_period= (YEAR(TODAY()) -2000)*10+ QUARTER(TODAY())-1
return
CALCULATE( SUM('Table'[Value]) , 'Table'[Period_Number] = _Previous_period)+0

(4)Then we can put it on the visual and we can meet your need:

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ani26]

Hello Aniya, Thank you for providing your valuable time. I tried the method that you suggested, but as I said in my post I have a slicer on my page where I switch in between periods. As soon as I select Q2-23, the Opp C row disappears and it gives me the same results as before.
My requirement is even when I select Q2-23 in the slicer it should still show Opp C with a 0 or blank in the visual.

[v-yueyunzh-msft · Accepted solution]

Hi, @Ani26 

I's sorry , i may misunderstand your need . According to your description, you want to use the slicer to select the current prriod and you want to see the all "Opp".

If this , you need to click "New Table" to create a table :

Table 2 = VALUES('Table'[Opp])

Then we can modify the before measures:

Current quarter2 = var _curent_period= MAX('Table'[Period_Number])
var _cur_opp=MAX('Table 2'[Opp])
return
CALCULATE( SUM('Table'[Value]) , 'Table'[Period_Number] = _curent_period && 'Table'[Opp]=_cur_opp,ALL('Table'[Period]))+0

Previous Quarter2 = var _Previous_period= MAX('Table'[Period_Number])-1
var _cur_opp= MAX('Table 2'[Opp])
return
CALCULATE( SUM('Table'[Value]) , 'Table'[Period_Number] = _Previous_period && 'Table'[Opp]=_cur_opp, ALL('Table'[Period]))+0

Then we can put these fields on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ani26]

Thank you so much. This works as magic. Appreciate all your help.