Latest Enterprise DNA Initiatives

Sales between particular months

DAX Calculations
#1

Hi There,

Need one quick help regarding a DAX calculations, I need to figure out sales between Jul-19 to Feb-20 and need to make it permanent (even if I change slicer of month the same should not get changes). I have a Calendar table and my fiscal year starts from Jul to Jun. My dax measure is “Total Sales”.

I will not be able to paste my company’s data over here hence wont be able to paste sample data. (sorry for this inconvenience)

Regards
Harish Rathore

#2

@harishrathore
Please look at the following video to hide the confidential data before posting on the forum as without the PBIX and source files, providing a solution is very difficult

You can try the following measure though:

Total Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Dates[Date] ),
        Dates[Date] > DATE ( 2019, 7, 1 )
            && Dates[Date] <= DATE ( 2020, 2, 1 )
    ),
    REMOVEFILTERS ( Dates[Date] )
)

Thanks.

#3

@MudassirAli,

Just a couple of slight tweaks – I think the filter calculation should be inclusive of July 1, so it should be >=. Also, I don’t think you need the outer REMOVEFILTERS(), since you’re removing the filter on Dates[Date] with the inner ALL. Revised measure:

Total Sales = 

CALCULATE (
    [Sales],
    FILTER (
        ALL ( Dates[Date] ),
        Dates[Date] >= DATE ( 2019, 7, 1 )
            && Dates[Date] <= DATE ( 2020, 2, 1 )
    )
)

– Brian

1 Like
#4

Thanks @BrianJ.
AAH I removed the measure from PBI file but forgot to remove it from the DAX cleaner and yes, I missed the following part:

Thanks again!

1 Like
#5

Thanks a lot @MudassirAli and @BrianJ . solution from Brian worked like a charm. Will do my best to share PBIX file. I am very happy to be part of Enterprise Forum. Thanks again guys.

2 Likes