moving year sales

[jdtobon88]

Hi

I need to show in a graph the sales of the moving year based on the filter Month and year.

Example

yes filter April and year 2022

The graph should show me the last 12 months, that is, from May 2021 to April 2022. 

the table where the sales are is related to the dimdate table

thanks

[v-rzhou-msft · Accepted solution]

Hi @jdtobon88 ,

I suggest you to create an unrelated date table and then create a measure to calculate the dynamic result.

Date = 
ADDCOLUMNS( CALENDARAUTO(),"Year",YEAR([Date]), "Month",MONTH([Date]),"MonthName",FORMAT([Date],"MMMM"))

Here I create a sample with data from 2021/01/01 to today.

Measure = 
VAR _SELECTDATE = MAX('Date'[Date])
VAR _12MONTHBEFORE = EOMONTH(_SELECTDATE,-12)
RETURN
IF(MAX('Table'[Date])>_12MONTHBEFORE&&MAX('Table'[Date])<=_SELECTDATE,SUM('Table'[Sales]),BLANK())

Result is as below.

Best Regards,
Rico Zhou

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

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[sturlaws]

Hi, @jdtobon88,

Here is some pointers on How to Get Your Question Answered Quickly

Assuming you have slicers for year and month, you can write a measure something like this:

last 12 months =
VAR _year =
    CALCULATE ( SELECTEDVALUE ( date[year] ) )
VAR _month =
    CALCULATE ( SELECTEDVALUE ( date[month_number] ) )
VAR _date =
    DATE ( _year, _month, 1 )
VAR _date_prev_12 =
    DATE ( _year, _month - 11, 1 )
RETURN
    CALCULATE (
        SUM ( tablename[columnname] ),
        FILTER (
            ALL ( date ),
            date[start_of_month] >= _date_prev_12
                && date[start_of_month] <= _date
        )
    )

[jdtobon88]

Your formula did not help me, it does not give me data

I tried with the option in relative date, without filters it works for me but when I apply a filter of a month, it is not able to return and calculate the 12 months but it only brings me the filter of the current month

[sturlaws]

My bad, you would also need separate tables for month and year, like this