topic Re: Show the data for the latest 3 months as bar chart in DAX Commands and Tips

Show the data for the latest 3 months as bar chart

alearner — Sat, 24 Feb 2024 02:37:33 GMT

Need some advice. DAX sure is driving me nuts. I have attached a pbix file. All I have is 2 tables. One being date table and the other investments over the months. All I am trying to do is to show the latest 3 months of data as a bar chart. So if I am in the middle of Feb I would like to go 3 months back and go the START of the Month. So if I am in 23RF Feb my 3 monts prior should start from 1/Nov/23. While trying to understand STARTOFMONTH function I did a sample and tried to go to start of Feb but not sure why I get the result as 2/Feb/2024.

Can someone help me out understand and implement the same. Have gone nuts trying to implement this.

Link to the sample file https://www.dropbox.com/scl/fi/8qggzp50nosr94xudtjka/template.pbix?rlkey=pgzg71angsu1spmat6biayz9o&dl=0

Re: Show the data for the latest 3 months as bar chart

Jihwan_Kim — Sat, 24 Feb 2024 05:44:47 GMT

Hi,

I think the reason is because the column from the fact table is used in the formula.

STARTOFMONTH dax formula returns the first date of the month in the current context for the specified column of dates.

Please try using the column from the calendar table.

STARTOFMONTH function (DAX) - DAX | Microsoft Learn

latest3montinv = var _lastperiod3 = CALCULATE(STARTOFMONTH( LASTNONBLANK(DateT[Date],[TotalInvest]))) return _lastperiod3

Or, if you have to use the fact table in the formula, please try something like below.

EOMONTH function (DAX) - DAX | Microsoft Learn

latest3montinv V2 = VAR _lastperiod3 = EOMONTH ( LASTNONBLANK ( Investment[NAV_Dt], [TotalInvest] ), -1 ) + 1 RETURN _lastperiod3

Re: Show the data for the latest 3 months as bar chart

alearner — Sun, 25 Feb 2024 13:38:32 GMT

hI Jihwan_Kim thanks for your response. Can you help me explain why:

lastdt =

var _lastperiod = CALCULATE(LASTNONBLANK(DateT[Date],[TotalInvest]))

return _lastperiod

returns 16-2-2024

but

lastdt =

var _lastperiod = CALCULATE(LASTNONBLANK(DateT[Date],sum(Investment[Ammount_Invested])))

return _lastperiod

returns

31-12-2024

Both the DAX formulas are the same but why different results.

[TotalInvest] is a measure which is caluclated as

sum(Investment[Ammount_Invested]).

I am new to DAX and trying to learn it

Have used the same file

Re: Show the data for the latest 3 months as bar chart

Jihwan_Kim — Sun, 25 Feb 2024 15:17:47 GMT

Hi,

In my opinion, it is related to the context transition.

When iterating rows to find the last non blank, inserting measure itself already applies context transition.

However, inserting the whole formula does not apply context trasition. In order to have the same result, please try something like below.

lastdt = VAR _lastperiod = CALCULATE ( LASTNONBLANK ( DateT[Date], CALCULATE ( SUM ( Investment[Ammount_Invested] ) ) ) ) RETURN _lastperiod

Understanding Context Transition - SQLBI

The above link helps to understand about context transition.

Thanks.