Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
14 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
16 | |
13 |