cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sidhu
Frequent Visitor

Getting Same Period Last Year without date table

I have table with moving dates for months , below is the format. Now I need to calculate the time intelligence functions like Same Period Last Year, if the user selects 2022 P1,2022 P2, I need to display the values for same period last year like 2021 P1, 2021 P2. what is the easy way to do this?

Sidhu_0-1669673441527.png

 

2 REPLIES 2
ghaines
Helper I
Helper I

I really would recommend a date table, you could construct one from scratch and then add the periods required and it opens up many other options.  That also gives you the flexibility you get when your date ranges are non contiguous.

 

However...

 

You could use something like this:

 

MeasureName = 
VAR PeriodStart = EDATE(SELECTEDVALUE('Date Table'[Start Date]), -12)
VAR PeriodEnd = EDATE(SELECTEDVALUE('Date Table'[End Date]), -12)

RETURN
SUMX(
    VALUES('Date Table'[Month)),
    CALCULATE(SUM('Fact Table'[Value]),
        FILTER(VALUES('Fact Table'[Transaction Date]),
            'Fact Table'[Transaction Date] >= PeriodStart &&
            'Fact Table'[Transaction Date] <= PeriodEnd
        )
    )
)

 

I have not tested this code because I am at work and just throwing something together while data loaded, it could have some overlooked context issues so please test.  For averages you're better off making two measures like this and then dividing e.g. AvgPrice = DIVIDE([Total Price], [Total Units])

djurecicK2
Memorable Member
Memorable Member

@Sidhu ,

 The easiest way to do this is with a date table- here is some additional information:

https://kteam.ch/why-almost-every-power-bi-report-needs-a-date-table/

 

https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

 

 

Please accept as solution if this has answered the question- thanks!


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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.