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
ErikOmni
Helper I
Helper I

Previous Year Calculation -1 day

Hi!

 

So I'm trying to "move" alla previous year metrics one day.  For example; 2022-11-18 should be compared to 2021-11-19. I have solved it on a line by line basis but the sum of days doesn't work. I realize that it must be down to my "max"-part of the calculation but can't figure out an alternative to work.

 

See below example, the total takes the latest date.

 

 

ErikOmni_0-1669024353309.png

 

 

Formula looks like this:

 

CALCULATE([Sales], FILTER(ALL('Date'[Date]), 'Date'[Date]=MAX(OrderData[OrderDate])-364))
 
 
The goal is for PY Sales to look like this:
 
ErikOmni_2-1669024571893.png

 


 

 

Any ideas?

 

 

 

 

1 ACCEPTED SOLUTION

Ok, try this then:

PY =
SUMX (
    VALUES ( Table[Date] ),
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date]
                = MAX ( OrderData[OrderDate] ) - 364
        )
    )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

Try:

PY with total = 
SUMX(VALUES(Table[Date]), [PY Sales])




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown 

 

Hi! The solution works, but its' not ideal because then I need to create 2 metrics for all PY calculations.

 

In other words, ideally I would just have 1 metric for PY rather than having to create first the "YTD"* PY calculation and then the sum calculation of that.

 

*YTD = Max date being last orderdate

Ok, try this then:

PY =
SUMX (
    VALUES ( Table[Date] ),
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date]
                = MAX ( OrderData[OrderDate] ) - 364
        )
    )
)




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@PaulDBrown Thank you!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.