Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Delmekka
Frequent Visitor

Create Custom Period - 4 months period no QTD

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

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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

 

View solution in original post

8 REPLIES 8
v-luwang-msft
Community Support
Community Support

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
Super User
Super User

@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
Super User
Super User

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] ) 
)

 

jdbuchanan71_0-1651668817009.png

 

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

 

Delmekka_0-1651669632071.png

 

We cannot

jdbuchanan71
Super User
Super User

@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
Community Champion
Community Champion

@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
Super User
Super User

@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

 

2022-05-03_6-41-52.png

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] )
    )
)

2022-05-03_7-05-36.png

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 !

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.