Data segmentation by month filter the values of the current month + the previous month from a SUMX

[carddosogabriel]

Good afternoon guys, how are you? I'm having a problem with a DAX function I've made:

First I made an Index that would return me 1 if a purchase had been made within a range of 8W:

1 - I8W = IF(

    CALCULATE(

        SUM('TABLE'[Net Sales Volume]),

            DATESINPERIOD('TABLE'[Data], MAX('TABLE'[Data]),-56,DAY)) > 0

    ,1

    ,0

)

The second formula sums I8W over the 8-week interval:

Sum_I8W = SUMX(ALL('DataBase'[SAP]), [I8W])
However, when I segment the data by month, the metric only returns the sum of the selected month, excluding the previous month.

I would like it to return August + July in the data segmentation when I check the August option, like this way without having to check the 2 boxes or when I select the month in a graph

Can anyone give me some help/insight with this?

Thank you very much and Merry Christmas to all! 🙂

[carddosogabriel]

It worked!!!!

@Ashish_Mathur, thank you so much!!! I've been thinking about this for days.

Can you tell me why? Why do we have this difference in Value with Dcalendario vs TableData?

Cheers, Merry Christmas!!!

[Ashish_Mathur]

You are welcome.  For any date calculation, creation of a Calendar Table is a must.  Creation of a Calendar Table, allows usage of the Date intelligence functions.

[Ashish_Mathur · Accepted solution]

Hi,

Try this approach.

1. Create a Calendar Table with calculated column formilas for Year, Month name and Month number.  Sort the Month name column by the Month number
2. Create a relationship (Many to One and Single) from the Date column of Table to the Date column of the Calendar Table
3. to your visual/slicer/filter, drag Date//Month name from the Calendar Table
4. Write this measure

Measure = calculate(sum(table[Net sales volume]),datesbetween(calendar[date],max(calendar[date])-55,max(calendar[date])))

Hope this helps.