Advise on optimizing multiple DAX measures

[dandreev]

Hello,
I have built a table with DAX Measures. 

Product	5/1/2023	6/1/2023	7/1/2023	3 MonthsTotal	Avg	Variance
A	11	10	5	26	13	13
B	12	20	15	47	16	31
C	13	30	25	58	19	39
Total	36	60	55	131	48	83

I have the following column measures for the months:
 

Mnth1 = 
VAR Month1Total =
    CALCULATE (
        DISTINCTCOUNT ( 'Product'[ProductID] ),
        //KEEPFILTERS ( VALUES ( 'DimDate'[YearMonth] ) ),
        AND (
            'DimDate'[Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
            'DimDate'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
        )
    )
RETURN
    IF ( ISBLANK ( Month1Total ), 0, Month1Total )

Second Month Column

Mnth2 = 
VAR Month2Total =
    CALCULATE (
        DISTINCTCOUNT ( 'Product'[ProductID] ),
        //KEEPFILTERS ( VALUES ( 'DimDate'[YearMonth] ) ),
        AND (
            'DimDate'[Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 2, 1 ),
            'DimDate'[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () )-1, 1 )
        )
    )
RETURN
    IF ( ISBLANK ( Month2Total ), 0, Month2Total )

My model is classic star model with one fact table and 4 dienssion tables and Date table.

I created another matrix visual with calculation groups, but report's requstors didn't like the extra row that was added to column headers. That was the reason I went to building a measure for each column. My questions are
How can I combine the measures? The original table has over 15 columns. 
Also, is there a way to create measure names that will change there names based on current date? For example, if today is September the headears chnge from July to August and June to July, etc.
Thank you for your time!

 

[Sahir_Maharaj]

Hello @dandreev,

1. Instead of creating separate measures for each month, you can use Time Intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR to calculate the total and the same month's data for each month.

TotalQty = 
VAR SelectedDate = TODAY()
VAR PreviousMonthEndDate = EOMONTH(SelectedDate, -1)
VAR TwoMonthsAgoEndDate = EOMONTH(SelectedDate, -2)
VAR ThreeMonthsAgoEndDate = EOMONTH(SelectedDate, -3)

RETURN
SWITCH(TRUE(),
    SELECTEDVALUE('DimDate'[Date]) = SelectedDate, CALCULATE(DISTINCTCOUNT('Product'[ProductID])),
    SELECTEDVALUE('DimDate'[Date]) = PreviousMonthEndDate, CALCULATE(DISTINCTCOUNT('Product'[ProductID]), DATESYTD('DimDate'[Date])),
    SELECTEDVALUE('DimDate'[Date]) = TwoMonthsAgoEndDate, CALCULATE(DISTINCTCOUNT('Product'[ProductID]), SAMEPERIODLASTYEAR(DATESYTD('DimDate'[Date]))),
    SELECTEDVALUE('DimDate'[Date]) = ThreeMonthsAgoEndDate, CALCULATE(DISTINCTCOUNT('Product'[ProductID]), SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR(DATESYTD('DimDate'[Date])))),
    0
)

2. To dynamically change the measure names based on the current date, you can use DAX variables and concatenate them in the measure names.

DynamicMeasureName = 
VAR SelectedDate = TODAY()
VAR Month1 = FORMAT(EOMONTH(SelectedDate, -1), "MMM")
VAR Month2 = FORMAT(EOMONTH(SelectedDate, -2), "MMM")
VAR Month3 = FORMAT(EOMONTH(SelectedDate, -3), "MMM")

RETURN
SWITCH(SELECTEDVALUE('DimDate'[Date]),
    SelectedDate, "Qty " & Month1,
    EOMONTH(SelectedDate, -1), "Qty " & Month2,
    EOMONTH(SelectedDate, -2), "Qty " & Month3,
    BLANK()
)

Should you require further assistance please do not hesitate to reach out to me.

[dandreev]

Hi Sahir,
Thank you for your time!
I have hard time to make the solution work. DynamicMeasureName is returning blank. I think the reason is mismatch of the data type. 
TotalQty measure is also returning blank. 
Thank you!

[dandreev · Accepted solution]

I built a view in SQL server and resolve the problem. My SQL skills are better than my DAX skills. Thank you to everyone who helped or read the ticket.