cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Show the data for the latest 3 months as bar chart

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.

1 ACCEPTED SOLUTION
Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

3 REPLIES 3
Frequent Visitor

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
Super User

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

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

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
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors