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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Have Trailing 12 months (name), MTD, QTD, and YTRD in same column

Hi All,

 

Appreciate any help possible.

 

I am trying to come up with a solution where I need to have trailing 12 months name along with MTD, QTD and YTD in the same drop down filter.
I am able to do QTD,MTD,YTD in a seperate column but not sure how to bring it in to the same filter beneath the month names in the filter.
When selecting QTD, the dashboard should filter for current Quarter and while selecting any month, the selected month numbers should be reflected. The months and the time frames should be dynamic based on the current date as well.

 

I have a star schema with a general date dimension joined to fact table.


Desired Output:

pdusi_0-1600395066613.png

 



1 ACCEPTED SOLUTION
Anonymous
Not applicable

Based on the above proposed solution, I am able to get the month-Year and the QTD,YTD,MTD in the same column. But when I select QTD, I want only those dates corresponding to this quarter to be displayed. This is not possible as there is no join between the test table and the calendar table.

 

I built up on the above logic and came up with this solution. ( browsed a few online sites as well)

 

Step 1: The MTD, QTD, YTD table: 

MTD/QTD/YTD Selection = 

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate), QUARTER(Query2[Cal_Dt]) = QUARTER(TodayDate) )

VAR MonthStart = CALCULATE(STARTOFMONTH(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate), MONTH(Query2[Cal_Dt]) = MONTH(TodayDate) )

VAR Result =

UNION (

ADDCOLUMNS (

CALENDAR ( YearStart, TodayDate ),

"Selection", "YTD"

),

ADDCOLUMNS (

CALENDAR ( QuarterStart, TodayDate ),

"Selection", "QTD"

),

ADDCOLUMNS (

CALENDAR ( MonthStart, TodayDate ),

"Selection", "MTD"

)

)

RETURN

Result
 
Step 2: Brought in a date dimension .

Step 3: union both tables as suggested.
Dropdown = UNION(

SELECTCOLUMNS(FILTER(FILTER('Date','Date'[Cal_Dt]>=TODAY()-365),'Date'[Cal_Dt]<=today()),"month",'Date'[Mth_Nm],"date",'Date'[Cal_Dt]),

SELECTCOLUMNS('MTD/QTD/YTD Selection',"format",'MTD/QTD/YTD Selection'[Selection],"date",'MTD/QTD/YTD Selection'[Date]))
 
I wanted only last 12 months to be displayed for selection.
 
And the joining is based on the date columns on all 3 tables.
 
 

SO now, when I use the selection column to selected QTD, I get to filter only the months in the current Quarter.

 

Joins:

 

JoinsJoins

 

Selction

 

Drop down selectionDrop down selection

 
 
Thanks for your suggestions. Now I am able to get the desired output.

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

a1.png

Calendar(a calculated table):

Calendar = CALENDARAUTO()

 

Test(a calculated table):

Test = 
UNION(
    DISTINCT('Table'[YearMonth]),
    ROW("YearMonth","MTD"),
    ROW("YearMonth","QTD"),
    ROW("YearMonth","YTD")
)

 

There is a relationship between 'Calendar' and 'Table'. You may create a measure as below.

Result = 
IF(
    HASONEVALUE('Test'[YearMonth]),
    SWITCH(
        SELECTEDVALUE('Test'[YearMonth]),
        "MTD",
        CALCULATE(
            SUM('Table'[Vaue]),
            DATESMTD('Calendar'[Date])
        ),
        "QTD",
        CALCULATE(
            SUM('Table'[Vaue]),
            DATESQTD('Calendar'[Date])
        ),
        "YTD",
        CALCULATE(
            SUM('Table'[Vaue]),
            DATESYTD('Calendar'[Date])
        ),
        IF(
            SELECTEDVALUE('Table'[YearMonth])=SELECTEDVALUE('Test'[YearMonth]),
            SUM('Table'[Vaue])
        )
    )
)

 

Result:

a2.png

a3.png

a4.png

a5.png

 

Best Regards

Allan

 

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

 

Anonymous
Not applicable

Based on the above proposed solution, I am able to get the month-Year and the QTD,YTD,MTD in the same column. But when I select QTD, I want only those dates corresponding to this quarter to be displayed. This is not possible as there is no join between the test table and the calendar table.

 

I built up on the above logic and came up with this solution. ( browsed a few online sites as well)

 

Step 1: The MTD, QTD, YTD table: 

MTD/QTD/YTD Selection = 

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate), QUARTER(Query2[Cal_Dt]) = QUARTER(TodayDate) )

VAR MonthStart = CALCULATE(STARTOFMONTH(Query2[Cal_Dt]), YEAR(Query2[Cal_Dt]) = YEAR(TodayDate), MONTH(Query2[Cal_Dt]) = MONTH(TodayDate) )

VAR Result =

UNION (

ADDCOLUMNS (

CALENDAR ( YearStart, TodayDate ),

"Selection", "YTD"

),

ADDCOLUMNS (

CALENDAR ( QuarterStart, TodayDate ),

"Selection", "QTD"

),

ADDCOLUMNS (

CALENDAR ( MonthStart, TodayDate ),

"Selection", "MTD"

)

)

RETURN

Result
 
Step 2: Brought in a date dimension .

Step 3: union both tables as suggested.
Dropdown = UNION(

SELECTCOLUMNS(FILTER(FILTER('Date','Date'[Cal_Dt]>=TODAY()-365),'Date'[Cal_Dt]<=today()),"month",'Date'[Mth_Nm],"date",'Date'[Cal_Dt]),

SELECTCOLUMNS('MTD/QTD/YTD Selection',"format",'MTD/QTD/YTD Selection'[Selection],"date",'MTD/QTD/YTD Selection'[Date]))
 
I wanted only last 12 months to be displayed for selection.
 
And the joining is based on the date columns on all 3 tables.
 
 

SO now, when I use the selection column to selected QTD, I get to filter only the months in the current Quarter.

 

Joins:

 

JoinsJoins

 

Selction

 

Drop down selectionDrop down selection

 
 
Thanks for your suggestions. Now I am able to get the desired output.
amitchandak
Super User
Super User

@Anonymous ,

 

You have create a new table like this And then you have create measure based on selection

union(
summarize(filter(Date, Date[Date]>=today()-365), Date[Month]),
ROW("Month", "MTD"),
ROW("Month", "QTD"),
ROW("Month", "YTD")
)

vivran22
Community Champion
Community Champion

Hello @Anonymous 

 

You may need to use Calculation Groups in Power BI. There are some excellent artilces which you may refer to:

 

https://www.sqlbi.com/articles/introducing-calculation-groups/

 

https://www.youtube.com/watch?v=a4zYT-N-zsU

https://www.youtube.com/watch?v=vlnx7QUVYME

 

Cheers!
Vivek

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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