Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PatilPrasad027
Resolver I
Resolver I

Display YTDPrevious month and YTDtwoMonthsAgo

Hi Community..!!

PatilPrasad027_1-1716807727508.png

Please refer above pic,

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]))
 
Please help me out to reach the above result.
 
Thanks,
Prasad Patil
1 ACCEPTED SOLUTION

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

View solution in original post

5 REPLIES 5
danextian
Super User
Super User

Hi @PatilPrasad027 ,

 

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.

danextian_0-1716809562352.png

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] )
    )
)

Please see attached sample pbix.










Did I answer your question? Mark my post as a solution!


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.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thank u @danextian 

Your solution also works..!!

rajendraongole1
Community Champion
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.

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

Great! @PatilPrasad027 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors