cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
msp_25
New Member

Dax on last n days

Hello, 

 

 

Can anyone help me to calculate last n days data but it should not consider the current date in calculation.  

 

Thank you.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

The disconnected table shown in the below is created by using Numeric range new parameter feature.

 

Jihwan_Kim_2-1672040527183.png

 

 

 

Jihwan_Kim_1-1672040439020.png

 

Value total: = 
SUM( Data[Value] )

 

Last N days value total: =
VAR _Ndays = [LastNdays Value]
VAR _window =
    WINDOW (
        - _Ndays,
        REL,
        -1,
        REL,
        ALL ( 'Calendar'[Date] ),
        ORDERBY ( 'Calendar'[Date], ASC )
    )
RETURN
    IF ( COUNTROWS ( _window ) >= _Ndays, CALCULATE ( [Value total:], _window ) )

 

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

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

The disconnected table shown in the below is created by using Numeric range new parameter feature.

 

Jihwan_Kim_2-1672040527183.png

 

 

 

Jihwan_Kim_1-1672040439020.png

 

Value total: = 
SUM( Data[Value] )

 

Last N days value total: =
VAR _Ndays = [LastNdays Value]
VAR _window =
    WINDOW (
        - _Ndays,
        REL,
        -1,
        REL,
        ALL ( 'Calendar'[Date] ),
        ORDERBY ( 'Calendar'[Date], ASC )
    )
RETURN
    IF ( COUNTROWS ( _window ) >= _Ndays, CALCULATE ( [Value total:], _window ) )

 

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


Mahesh0016
Solution Sage
Solution Sage

Measure = calculate(TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>, [<Order>]]…]),table[date]<MAX(table[date]))
@msp_25 If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors