QTD, Last Year Same QTR, YTD, YTD Last Year

[Jeo_Thomas]

Greetings,

I'm new to Power BI and dax, so please forgive me if my inquiry makes no sense.

In my company, I'm attempting to create a quarterly report. The source data contains some information that should be ignored when constructing the report because it is from an unfinished quarter. I can't delete it as it forms the source of other reports too.

After some research I was able to create a measure to calculate QTD thanks to @amitchandak by using the below DAX

" QTD-Submission Count = CALCULATE([Submission count],DATESQTD( ENDOFQUARTER(dateadd('1.Date_First Receipt Date'[Date],-1,QUARTER)))) "

But I am unable to find a solution to calculate

1. YTD - that only includes the quarter that is complete and ignores the incomplete quarter.

2. Last Year same Quarter  - Which shows the QTD data for last year which the same time frame of the current complete quarter.

3. YTD Last Year - YTD data for the last year which which the same time frame of this years completed quarter.

Any help would be highly appreciated.

[v-henryk-mstf]

Hi @Jeo_Thomas ,

I agree with @TomMartens  suggestion. If the problem has been solved, you can mark the correct reply as the standard answer to help the other members find it more quickly. If there is still confusion, please point out.

Looking forward to your reply.

Best Regards,
Henry

[TomMartens]

Hey @Jeo_Thomas ,

if there is a business rule that defines if a quarter is closed or not you can create a calculated column that flags this automatically.

If there is no such business rule you have to do this automatically, e.g create a table that contains a year column and a quarter column. Use Power Query to join both tables and create the "complete column" based on the Excel information.

Regards,

Tom 

[Jeo_Thomas]

Thanks, I created a master source file to flagging Closed Quarter as "Yes" or "No", then I used "Related" function to join the master table to date table. Is this what you meant?

[TomMartens · Accepted solution]

Hey @Jeo_Thomas ,

welcome to Power BI 🙂

I think it's a good idea reading this article: https://www.daxpatterns.com/time-patterns/ this article provides solutions for almost everything that relates to time.

It explains why it is important to use a dedicated Calendar table, as this almost mandatory for any data model.

Then you can create a new column in this Calendar table like "completed" and mark all days that are inside a Completed Quarter with "Yes" and all the other days with "No".

Then you can add something like this

CALCULATE(
    <Time Intelligence Function>
    , 'Calendar'[Completed Quarter] = "Yes"
)

Hopefully, this provides some idea on how to tackle your challenge.

Regards,

Tom

[Jeo_Thomas]

It sounds fantastic; I will definitely give it a shot.

I already have a date table with a quarter column; is there a method to automatically flag dates with complete quarter? Only manually identifying it comes to my mind when I think about it.

Please forgive me if I am asking too much.