cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jd8766
Helper II
Helper II

Measure to show sales based on date selection vs the same period before?

Hello.

I have a Sales table and a measure that is a simple (Countrows[Sales]) to show me the number of sales within a certain period. I have a sold date table attached to this.

I want a measure that will dynamically calculate the sales variance based on the selected period... so if I select sold date between '01 nov 2022' and '3 nov 2022' this will show me sales for the '29th oct 2022' to '31st october 2022' as there are 3 days selected in my slicer, it will show the 3 days prior.

Another example, if I select '01 jan 2022' to '31 dec 2022' it will show me the sales for '01 jan 2021' and '31 dec 2021' as a full year is selected.

Is this possible? What would be the best way to achieve this in a measure?

Many thanks

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @jd8766 ,

 

Try formula like below:

Amount_filter = sum(FactInternetSales[SalesAmount])
To-Year = TOTALYTD([Amount_filter],M_DimDate[Date])
Last-To-Year = 
CALCULATE (
    [Amount_filter],
    DATESBETWEEN (
        M_DimDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( M_DimDate[Date] ) ) ),
        LASTDATE ( M_DimDate[Date] )
    )
)

 

vhenrykmstf_0-1668413413739.png

If the problem is still not resolved, please provide test data and expected result screenshoots. Looking forward to your reply.


Best Regards,
Henry


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

 

 

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @jd8766 ,

 

Try formula like below:

Amount_filter = sum(FactInternetSales[SalesAmount])
To-Year = TOTALYTD([Amount_filter],M_DimDate[Date])
Last-To-Year = 
CALCULATE (
    [Amount_filter],
    DATESBETWEEN (
        M_DimDate[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( M_DimDate[Date] ) ) ),
        LASTDATE ( M_DimDate[Date] )
    )
)

 

vhenrykmstf_0-1668413413739.png

If the problem is still not resolved, please provide test data and expected result screenshoots. Looking forward to your reply.


Best Regards,
Henry


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

 

 

DimaMD
Super User
Super User

Hi, @jd8766 try it

 

var min_date = Min('dates'[Date])
var min_date_LY = DATE(
    YEAR(
        MIN(
            'dates'[Date]))-1, 
            MONTH(min_date),
             DAY(min_date))
var max_date = Max('dates'[Date])
var max_date_LY = DATE(YEAR(MAX('dates'[Date]))-1, MONTH(max_date), DAY(max_date))
Return
CALCULATE([Sales], DATESBETWEEN('dates'[Date], min_date_LY, max_date_LY))

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Hi, it doesn't work.. when I select date range '01 nov 2022' to '03 nov 2022' the minimum date this brings back is '01 nov 2021' when it should bring back '27 oct 2022'

as 3 days are selected in the slicer, the measure should look 3 days prior to the first date selected if that makes sense?

jd8766_0-1667486545725.png

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors