Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Can anyone help me to calculate last n days data but it should not consider the current date in calculation.
Thank you.
Solved! Go to Solution.
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.
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.
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.
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.
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.
User | Count |
---|---|
98 | |
91 | |
84 | |
72 | |
67 |
User | Count |
---|---|
114 | |
103 | |
100 | |
72 | |
64 |