Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
alearner
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. 

 

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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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

 

 

Jihwan_Kim_0-1708753281192.png

 

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
alearner
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

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.

 

 

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.


Go to My LinkedIn Page


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

 

 

Jihwan_Kim_0-1708753281192.png

 

 

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.


Go to My LinkedIn Page


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors