Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PorterHaus
Frequent Visitor

Is it possible to calculate sales last year using period instead of date?

I'm trying to recreate a bar chart featuring this years sales with a line representing last years sales. Similar to the one below:

The bar chart would feature this years sales and the line would be last years. The issue I'm running into is that my data doesn't have a full date so I'm having trouble creating a measure for last years sales. I'm using a table that has a column named Period which is basically a combination of the year and month number. For example, 201803 for this month. 

 

I've tried creating a measure shown below but I get an error since period isn't a date.

 

Revenue LY = CALCULATE(SUM(Revenue[Revenue]), SAMEPERIODLASTYEAR(DimDate[Period]))

 

2 REPLIES 2
Zubair_Muhammad
Community Champion
Community Champion

@PorterHaus

 

If the period column is formatted as number...

May be you can use this

 

Revenue LY =
CALCULATE (
    SUM ( Revenue[Revenue] ),
    FILTER (
        ALL ( Revenue[Period] ),
        Revenue[Period]
            = SELECTEDVALUE ( Revenue[Period] ) - 100
    )
)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad

 

Thank you for the reply.

 

I followed your suggestion and added a few VALUE functions because Period is stored as text. Despite this, no values were returned.

 

Revenue LY = 
CALCULATE(
    SUM (Revenue[Revenue]),
    FILTER(
        ALL(Revenue[Period]),
        VALUE(Revenue[Period]) = VALUE(Revenue[Period]) - 100
    )
)


Result:

Capture.PNG

 

 

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.