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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
jdtobon88
Helper I
Helper I

moving year sales

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

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1653371892113.png

 

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.

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1653371892113.png

 

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sturlaws
Resident Rockstar
Resident Rockstar

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
        )
    )

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

 

 

Captura de pantalla 2022-05-20 183650.jpg

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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