Line Chart: MTD QTD YTD to display data in dynamic months and days in Line Chart in Power BI Desktop

[Optimusprime_25]

Hello Team,

I have a below requirement where I have to display data according to the MTD, QTD and YTD in Line Chart with KPIs in Slicer.

YTD:

When the user selects Sales in slicer and YTD in anaother slicer then data should display from Jan to Till date in months in Line Chart.

QTD:

When the user selects Sales in slicer and QTD in anaother slicer then data should display from start of that particular quarter to Till date in months in Line Chart.

MTD:

When the user selects Sales in slicer and MTD in anaother slicer then data should display from start of that particular Month to Till date in days in Line Chart.

Thanks in Advance.

[v-jianboli-msft]

Hi @Optimusprime_25 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file  after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Optimusprime_25]

@v-jianboli-msft  Thanks for the solution, appreciate your support.

[v-jianboli-msft]

Hi @Optimusprime_25 

Please provide me with more details about the error of your DAX ， like this:

Or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[v-jianboli-msft · Accepted solution]

Hi Optimusprime_25,

Please follow these steps:

Create two new tables for slicer:

Then create measures for these two slicers separately:

Measure =

SWITCH (

    MAX ( 'FOR SLICER1'[Value] ),

    "SALES", SELECTCOLUMNS ( 'Table', "SALES", [SALES] ),

    "STOCK", SELECTCOLUMNS ( 'Table', "STOCK", [STOCK] ),

    "ORDERS", SELECTCOLUMNS ( 'Table', "ORDERS", [ORDERS] ),

    SELECTCOLUMNS ( 'Table', "INVOICE", [INVOICE] )

)

Measure 2 =

SWITCH (

    MAX ( 'FOR SLICER2'[Value] ),

    "YTD",

        IF (

            MAX ( 'Table'[Date] ) <= TODAY ()

                && MAX ( 'Table'[Date] ) >= DATE ( YEAR ( TODAY () ), 1, 1 ),

            1,

            0

        ),

    "MTD",

        IF (

            MAX ( 'Table'[Date] ) <= TODAY ()

                && MAX ( 'Table'[Date] ) >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),

            1,

            0

        ),

    IF (

        MAX ( 'Table'[Date] ) <= TODAY ()

            && QUARTER ( MAX ( 'Table'[Date] ) ) = QUARTER ( TODAY () )

            && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () ),

        1,

        0

    )

)

Apply Measure to the visual:

Apply Measure 2 to the filter:

Turn the single select of the slicers on:

Final output:

Best Regards,

Jianbo Li

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

[pbi_dev_acc22]

Hey 
I tried this solution for my own PBI.

But for some reason for the MTD calculation is showing some days in the previous month as well.

Same happens with QTD starts at 06/25 instead of 07/02 which is the first available date in the quarter.

Why is that?

This is the actual data  where sales are not 0.

This is the last date in the sales table is 09/04/2023 but sales is set to 0 and I have a filter excluding sales equal to 0.

The last date in my date table is 31/12/2023. This is because we will include forecasted sales in the near future.

I tried the measure with both Sales[Date} and Dim_date[date]  and I get same results. Any insights as what is wrong?

[Optimusprime_25]

@v-jianboli-msft Thanks for your response and the solution.

I have created Measure and Measure2

For Measure, i didn't afce any issues but for Measure2, I am facing below issue.

Measure 2 =

SWITCH (

    MAX ( 'FOR SLICER2'[Value] ),

    "YTD",

        IF (

            MAX ( 'Table'[Date] ) <= TODAY ()

                && MAX ( 'Table'[Date] ) >= DATE ( YEAR ( TODAY () ), 1, 1 ),

            1,

            0

        ),

    "MTD",

        IF (

            MAX ( 'Table'[Date] ) <= TODAY ()

                && MAX ( 'Table'[Date] ) >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ),

            1,

            0

        ),

    IF (

        MAX ( 'Table'[Date] ) <= TODAY ()

            && QUARTER ( MAX ( 'Table'[Date] ) ) = QUARTER ( TODAY () )

            && YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () ),

        1,

        0

    )

)

Could you please let me know what is the alternate solution to this.

Thanks in advance.

[CNENFRNL]

Typic senario in which the powerful calculation group and field parameters functionality come into play.

Let report readers use field parameters to change visuals (preview) - Power BI | Microsoft Docs