topic Re: Different measure for different account number in DAX Commands and Tips

Different measure for different account number

Jackie003 — Thu, 30 Jan 2025 10:45:33 GMT

Hello I have in my data model: Calendar table, FCT Parameters table, Forecast Costs table. In Forecast Costs table I have data from Jan 2024 till Oct 2024. I want to show data also for missing months.

I made my dax formula to check if it's work and it' ok:

Forecast=

VAR maxdate =

MAX ( 'Calendar'[Date])

RETURN

CALCULATE (

LASTNONBLANKVALUE ( 'Calendar'[Month], SUM ('Forecast Costs'[Quantity])*1.05 ),

ALL ( 'Calendar' ),

'Calendar'[Date] <= maxdate

)
I can see future months with last available value for OCT (In Nov and Dec I can see OCT Values). I want to rebuild my formula for specific account numbers. In FCT Parameters table I have "Rule" column ("Average", "Prev Month" - thats my text values). I want to display future months (NOV and DEC), but when my account number has "Average" rule I want to see in NOV and DEC average costs from Jan to Oct. For "Prev Month" rule i want to see previous month value. Does Anyone can help with that?

Re: Different measure for different account number

bhanu_gautam — Thu, 30 Jan 2025 11:53:57 GMT

Jackie003 , Try using below mentioned measure

Forecast =
VAR maxdate = MAX('Calendar'[Date])
VAR currentRule = SELECTEDVALUE('FCT Parameters'[Rule])
VAR result =
SWITCH(
currentRule,
"Average",
CALCULATE(
AVERAGEX(
DATESBETWEEN('Calendar'[Date], DATE(2024, 1, 1), DATE(2024, 10, 31)),
'Forecast Costs'[Quantity]
),
ALL('Calendar'),
'Calendar'[Date] <= maxdate
),
"Prev Month",
CALCULATE(
LASTNONBLANKVALUE('Calendar'[Month], SUM('Forecast Costs'[Quantity])),
ALL('Calendar'),
'Calendar'[Date] <= maxdate
),
// Default case if no rule matches
CALCULATE(
LASTNONBLANKVALUE('Calendar'[Month], SUM('Forecast Costs'[Quantity]) * 1.05),
ALL('Calendar'),
'Calendar'[Date] <= maxdate
)
)
RETURN
result

Re: Different measure for different account number

Jackie003 — Fri, 31 Jan 2025 13:13:49 GMT

Unfortunately, it does not work