cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

Display YTDPrevious month and YTDtwoMonthsAgo

Hi Community..!!

I am calculating YTD here,

1. I want to display  Feb result to march and march result to april and so on.

2. I want to display Feb result to April and March result to May and so on.

I have used below logic for calculating current month YTD.

YTD_CurrentMonth = CALCULATE(SUM(EDA_Trenline_Sales_Leo[sales]),DATESYTD(res_month[MONTH-1]))

Thanks,
1 ACCEPTED SOLUTION
Resolver I

Thank u @rajendraongole1

Its correct ,

just slight change in the calculation as per my requirement,

var current_date = MAX(res_month[MONTH-1])
var shift_date = EOMONTH(current_date,-1)
var result = CALCULATE([YTD_CurrentMonth],DATESINPERIOD(res_month[MONTH-1] ,shift_date,-1,MONTH))
RETURN result

just need to use -1 instade of +1
5 REPLIES 5
Super User

You can use OFFSET to get the YTD before the current row or before that but he exact formula will vary depending on the dimension in the visual.

``````YTD sum =
CALCULATE ( [Sales], DATESYTD ( d_Dates[Date] ) )
``````
``````YTD LM =
CALCULATE (
[YTD sum],
OFFSET (
-1,
ALLSELECTED ( d_Dates[Year], d_Dates[Month Short], d_Dates[Month Number] ),
ORDERBY ( d_Dates[Month Number] )
)
)
``````
``````YTD 2LM =
CALCULATE (
[YTD sum],
OFFSET (
-2,
ALLSELECTED ( d_Dates[Year], d_Dates[Month Short], d_Dates[Month Number] ),
ORDERBY ( d_Dates[Month Number] )
)
)
``````

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Resolver I

Thank u @danextian

Community Champion

Hi @PatilPrasad027 -  can you try with below logic

Shifted YTD Actuals =
VAR CurrentDate = MAX('Date'[Date])
VAR ShiftedDate = EOMONTH(CurrentDate, -1)
RETURN
CALCULATE(
[YTD Actuals],
DATESINPERIOD('Date'[Date], ShiftedDate, MONTH, 1)
)

it works for month, instead of month replace with Year and check.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Resolver I

Thank u @rajendraongole1

Its correct ,

just slight change in the calculation as per my requirement,

var current_date = MAX(res_month[MONTH-1])
var shift_date = EOMONTH(current_date,-1)
var result = CALCULATE([YTD_CurrentMonth],DATESINPERIOD(res_month[MONTH-1] ,shift_date,-1,MONTH))
RETURN result

just need to use -1 instade of +1
Community Champion

If this post helps, then please consider Accept it as the solution or give a kudos