Showing next 3 YearMonths and then apply it on a Quarter's level

[kostaszogo21]

Hello to all and my best wishes to everyone.
I'm fairly new to Power BI and i think i'll be in deep trouble without your help.
Let me explain my case:
I have a predictions measure, named "Backlog", and i had a request to show next 3 months' Backlog from the selected date.
I managed to solve it by using a disconnected Date Table ("SlicerTableYearMonth") and writing the following DAX:

3monthsRRBacklog_Correct =

    var SelectedMonth=min(SlicerTableYearMonth[YearMonthNo])

    var CurrentMonth=min(DateTable[YearMonthNo])

       return if(
                 CurrentMonth>SelectedMonth && CurrentMonth<SelectedMonth+4,

                [Backlog] ,blank())

and removing the DateTable[YearMonthNo] filters' interactions from my graph. 
The result is acceptable (although there might be an easier way). So  when selecting yearmonthno=Nov-2022 i get what i wanted:

So far, all good, backlog for 12/2022=4.627.497.
Next request is to show the same backlog but per Quarters and not Yearmonths, and also showing only future backlogs from selected date (as before).
So if i have a backlog in both 11/2022 and 12/2022 and my selected date is 11/2022, i don't want to depict the backlog of 11/2022 but only that of 12/2022 (exactly as it's shown in previous example, meaning 4.627.497).

The problem is that i can't solve it using the previous Measure with only having the Quarters dimension in my graph, i need Yearmonth as well because it's in my Backlog Measure.
So when i have Yearmonth in my table (here i have an hierarchy of Quarter and Yearmonth), i get the correct result:

When i keep only the Quarter, as the client asks, i get nothing:

I know that has something to do with the context, but although i tried, i couldn't find a solution.
So now i'm hoping the Power BI Santa Claus will help me...

Thanks in advance guys

[v-jianboli-msft · Accepted solution]

Hi @kostaszogo21 ,

Based on your description, I have created a simple sample:

Please try:

Measure = 
var _a = MAX('SlicerTableYearMonth'[Date])
var _b = FILTER('Table',[Date]>_a&&[Date]<=EOMONTH(_a,3))
return SUMX(_b,[Backlog])

Final output:

Best Regards,

Jianbo Li

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

[kostaszogo21]

Thanks  @v-jianboli-msft  

That worked as a charm!!!
I have another problem as well which i hope you can help me with.
I have a measure ("OpenBalance") that i want to show its values for the last 13 yearmonths.
I created a disconnected table (12monthsTable)and used the following measure to achieve the result i wanted to show:
12OpenBalance =
var SelectedMonth=max('12monthsTable'[YearMonthNo])
var CurrentMonth=max('DateTable'[YearMonthNo])
return if(CurrentMonth<=SelectedMonth && CurrentMonth>=SelectedMonth-100,[Open_Balance]/1000,blank())

I deactivated the 'DateTable'YearMonthNo slicer and all went great

I then wanted to calculate the Avg of that measure for the last 12 months,so i used the following measure, which also worked as i wanted:

Avg_OpenBalance =

var SelectedMonth=max('12monthsTable'[YearMonthNo])
var CurrentMonth=max('DateTable'[YearMonthNo])
return if(
    CurrentMonth<=SelectedMonth && CurrentMonth>SelectedMonth-    100 ,

calculate([12OpenBalance],

        filter(all('DateTable'[YearMonthNo]),
        'DateTable'[YearMonthno]<=MAX('DateTable'[YearMonthNo]) && 'DateTable'[YearMonthNo]>MAX('DateTable'[YearMonthNo])-100
)

)/12
)

Please note that in that table the 'DateTable'YearMonthNo is activated.
So far all good. 
Then i want to apply the same for another measure (WIP).
What is different in this measure is that in WIP i have to sum all WIP-reb_actuals prior to the selected YearMonthNo, so in order to achieve this i used the following measure in order to show the last 13 yearmonths:

 WIP =
var SelectedMonth=max('12monthsTable'[YearMonthNo])
var CurrentMonth=max('DateTable'[YearMonthNo])
return if(CurrentMonth<=SelectedMonth && CurrentMonth>=SelectedMonth-100 ,

calculate(
CALCULATE(
[WIP-reb_Actuals],
filter(
all(DateTable),
DateTable[YearMonthNo]<=MAX(DateTable[YearMonthNo])
)
)
And i got what i wanted:

But then when i want to show the SUM of that measure for the last 12 months (in order to divide it afterwards with 12 in order to get the AVG) as i did in Avg_OpenBalance before it doesn't work.
The measure i use is the following:

SUM_WIP =

var SelectedMonth=max('12monthsTable'[YearMonthNo])
var CurrentMonth=max('DateTable'[YearMonthNo])
return if(
    CurrentMonth<=SelectedMonth && CurrentMonth>SelectedMonth-    100 ,

calculate([WIP],

        filter(all('DateTable'[YearMonthNo]),
        'DateTable'[YearMonthno]<=MAX('DateTable'[YearMonthNo]) && 'DateTable'[YearMonthNo]>MAX('DateTable'[YearMonthNo])-100
)

)
)

The 'DateTable'YearMonthNo slicer is activated (as in Avg_OpenBalance before) and what i get is:

which is the number i have in Nov-2022 in the previous graph, because thatis the YearmonthNo i have selected in my slicer.
Something is not working in the filter of the last dax.
Can you please help me figure it out?

I really appreciate all the help i can get ...

Kostas

 

[FreemanZ]

hi @kostaszogo21 
when you say
"Next request is to show the same backlog but per Quarters and not Yearmonths, and also showing only future backlogs from selected date (as before). So if i have a backlog in both 11/2022 and 12/2022 and my selected date is 11/2022, i don't want to depict the backlog of 11/2022 but only that of 12/2022 (exactly as it's shown in previous example"

by per Quarters, seems actually you mean the next three months, different from the Q1 you put in your table visual.
So it is not a valid requirement, i would say.

p.s. it returns blank, because your measure relies on the context of DateTable[YearMonthNo] column, which is missing in the visual.