Forum Discussion

SammyNed's avatar
SammyNed
Helper I
2 years ago
Solved

3 full months trailing graph

Hi Everyone

 

Can someone please assist?

 

My stakeholders are excel lovers and want a view as below (dummy excel data).

 

If they select a month in the slicer, then the graph must show

  • the trailing 3 months of sales and previous year sales;
  • and then the forecast and target for that selected slicer month.

 

 

 

 

My attempts:

 

So i created a seperate date table, that does not have a relationship to the other tables and then referenced it in the measure below to get the sales for the selected slicer date (and 3 months trailing).

 

3MonthSales =
    VAR CurrentDate = MAX('Previous Date'[EndofMonth])
    VAR SelectedDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-3,DAY(CurrentDate))
    VAR Result =
    CALCULATE(sum[sales],
    Filter( Table,table[date]>= SelectedDate && Table[date]<= CurrentDate))
RETURN
Result
 
Problem 1:
However practically i get more or sometimes less than 3 exact months, as seen in the powerbi graph below. I believe this is because of the number of days in a month, but i want the full 3 months (selected month and 2 prior; if the selected month is not complete yet, then still only show me the incomplete month and 2 full months prior.)
 
Problem 2:
How can I get the previous year sales, this calculation does not work for previous year?

 

Thanking you in advance

 

šŸ˜

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi SammyNed ,

     

    Thanks for the reply from SachinNandanwar , please allow me to provide another insight: 

     

    I use these sample data to demonstrate.

     

    1. create a calculation table. And adjust the date format.

    Table =
DISTINCT(ā€˜financialsā€™[Date])

     

    2. create MEASURE.

    MEASURE =
VAR _cur_mon =
EOMONTH ( SELECTEDVALUE ( ā€˜Tableā€™[Date] ), 0 )
VAR _3_mon =
EOMONTH ( SELECTEDVALUE ( ā€˜Tableā€™[Date] ), -3 ) + 1
RETURN
IF (
MAX ( ā€˜financialsā€™[Date] ) <= _cur_mon
&& MAX ( ā€˜financialsā€™[Date] ) >= _3_mon,
1
)


    3. Filter the data in the bar chart where MEASURE is 1.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi SammyNed ,

     

    Thanks for the reply from SachinNandanwar , please allow me to provide another insight: 

     

    I use these sample data to demonstrate.

     

    1. create a calculation table. And adjust the date format.

    Table =
DISTINCT(ā€˜financialsā€™[Date])

     

    2. create MEASURE.

    MEASURE =
VAR _cur_mon =
EOMONTH ( SELECTEDVALUE ( ā€˜Tableā€™[Date] ), 0 )
VAR _3_mon =
EOMONTH ( SELECTEDVALUE ( ā€˜Tableā€™[Date] ), -3 ) + 1
RETURN
IF (
MAX ( ā€˜financialsā€™[Date] ) <= _cur_mon
&& MAX ( ā€˜financialsā€™[Date] ) >= _3_mon,
1
)


    3. Filter the data in the bar chart where MEASURE is 1.

     

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    Best Regards,

    Clara Gong

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

    • SammyNed's avatar
      SammyNed
      Helper I
      2 years ago

      thank you Anonymous . This really helped. I think my issue was with the date table (it was in days and not months)