Monthly figures and QTD, YTD, MTD on same matrix visual

[N-msft]

Hi,

I am wondering if this can be achieved in PBI.

How can I bring in the data for last 6 months and showing MTD/QTD/YTD performance as well in a single table or matrix visual.

Categories	Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	MTD Actual	MTD Planned	Variance	YTD Actual	YTD Planned	Variance
Travel	333	465	597	729	861	993	1125	340	-785	5103	2380	-2723
Home	230	234	238	242	246	774	1302	320	-982	3266	2240	-1026
Office	203	65	73	211	349	487	625	300	-325	2013	2100	87
Outdoor	123	67	11	322	87	492	897	350	-547	1999	2450	451

Appreciate your help in this matter.

[v-shex-msft]

Hi @N-msft,

You can create a new table with all months and custom period groups to use on matrix visual column field and replace the original table field.

Then you can wire a measure formula with SELECTEDVALUE and SWITCH functions to check the current row and column value as condition to look up and redirect to the different calculation.

Solved: Re: DAX SWITCH + SELECTEDVALUE() - Microsoft Fabric Community

Regards,

Xiaoxin Sheng

[N-msft]

Thanks @v-shex-msft ,

Can you elaborate on it little further as I sucks at DAX? May be by showing an example.

[v-shex-msft · Accepted solution]

HI @N-msft ,

You cna create a table with last date range string and add cusomt field vlaue QTD, YTD, MTD wiht index as sort by column properity.

Measure formula to check current category and period and redirect to different expressions: (current I put some placehoder in it, you can replace them to correspond expressions)

formula =
VAR currPeriod =
    SELECTEDVALUE ( NewTable[Period] )
VAR currCategory =
    SELECTEDVALUE ( 'Table'[Category] )
RETURN
    SWITCH (
        currPeriod,
        "MTD Actual", 100,
        "MTD Planned", 101,
        "QTD Actual", 200,
        "QTD Planned", 201,
        "YTD Actual", 300,
        "YTD Planned", 301,
        YEAR ( DATEVALUE ( currPeriod ) ) * 100
            + MONTH ( DATEVALUE ( currPeriod ) )
    )

Regards,

Xiaoxin Sheng