Create Custom Period - 4 months period no QTD

[Delmekka]

Hi there, 

Hope everyone is doing fine these days 🙂 

I had one question for you experts about creating custom period in PBI

For followup purposes, we analyse our KPI in YTD, MTD, MAT and Quadrimester (4month period).

Jan to April is Q1 and so on...

Right now, all i can get access to is MTD/YTD/MAT since there's a QTD but it's a 3 month period and not quite what i'm looking for. 

My question is, how to explain to PBI that Q1 is a period and needs to be adressed as such. 

Right now i've done a column with some TXT to note that these months belongs to this quadrimester but so far i have no great results in this..

So if you could help me out understanding how to create this custom period, i'll be so grateful !!!

Thanks in advance and have a good week everyone

[v-luwang-msft]

Hi @Delmekka ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[jdbuchanan71 · Accepted solution]

@Delmekka 

If you are using time intelligence functions like DATEADD and SAMEPERIODLASTYEAR you should have a dedicated dates table in your model and you need to mark it as the calendar table.
https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables

[jdbuchanan71]

If you are looking at it at the quad level you can just use something like this.

Sales Prior Period = 
CALCULATE(
    [Sales Amount],
    DATEADD(Dates[Date],-4,MONTH)
)

And proior year is just this.

Sales PY = 
CALCULATE ( 
    [Sales Amount],
    SAMEPERIODLASTYEAR ( 'Dates'[Date] ) 
)

[Delmekka]

Thanks 🙂 

Something funny is happening here lol..

I think your solution works fine with a settled database, because the moving months might be tricky in calculating quad sales... 

We cannot

[jdbuchanan71]

@Delmekka 

This is a good article that goes into detail about time intelligence in PowerBI.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[SpartaBI]

@Delmekka in case you created that 'DimDate'[Quadrimester] column (that has 3 values for each year) than you can use this measure for QuadrimesterToDate:

QuadrimesterToDate = 
VAR _max_date = MAX('DimDate'[Date]) 
VAR _current_year = SELECTEDVALUE('DimDate'[Year])
VAR _current_quadrimester = SELECTEDVALUE('DimDate'[Quadrimester])
RETURN
CALCULATE(
    [Measure],
    'DimDate'[Date] <= _max_date,
    'DimDate'[Trimester] = _current_quadrimester,
    'DimDate'[Year] = _current_year,
    REMOVEFILTERS('DimDate')
)

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos.

[jdbuchanan71]

@Delmekka 

Not sure if this is what you are looking for but you could add a column to your dates table to calcluate the field.

Quadrimester = 
VAR _Quad = ROUNDUP ( DIVIDE ( MONTH ( [Date] ), 4 ), 0 )
VAR _Year = YEAR ( [Date] )
RETURN "Q" & _Quad & "-" & _Year

Once we have this we can use it in a measure to get the Quad to date .

Quad To Date = 
CALCULATE ( 
    [Sales Amount],
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] <= MAX ( 'Dates'[Date] )
        && 'Dates'[Quadrimester] = SELECTEDVALUE ( 'Dates'[Quadrimester] )
    )
)

[Delmekka]

Thanks a lot for this answer, truly helped me a lot. 

Sorry if i'm asking too much but considering this, what shall be the way to calculate the Quad -1 (Previous quad) but also Same quad last year ? 

Think it might help a few people 🙂 

Thanks a lot for the help !