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

[Anonymous]

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:

[v-alq-msft]

Hi, @Anonymous 

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

Table:

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:

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 · Accepted solution]

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:

Joins

Selction

Drop down selection

 

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

[amitchandak]

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

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