Dashboard - As of date Revenue ( Prior Year)

[iqbal077]

I am looking show "as of date" data of last year in this years' dashboard. The table shown below takes a reading of the rooms sold and  room revenue at each date for all the months in the calendar. As an example, the screen shot below  shows the reading of sales data related to the month of June (2019). This tables assist us to compare the pacing of June 2020 Sales with 2019. I  am looking to do 2 things in the dashboard;

a) Automtaically switch the as of date (curren date) to last year - in this case today's date is 5/14/2020, the date slicer or filter autimcally goes back by 364 days to pull last years reading, so for the example, it will be 5/16/2019. 

b) Pull Revenue data for the current month same time last year ( as of date) and reading of the same for future months. As an example, as of 5/16/2019( the comparison date this year is 5/14/2020), the reported May revenue was 950k, June was 1,2mn, July was 1,1mn & Aug was 750k 

Appreciate any help.

Thanks in advance.

[v-alq-msft · Accepted solution]

Hi, @iqbal077 

Based on your description, I created data to reproduce your scenario. I attached the pbix file at the end.

Table:

Calendar:

Calendar = CALENDARAUTO( )

Calendar2:

Calendar2 = CALENDARAUTO()

There is a relationship between 'Table' and 'Calendar2'.

 You may create calculated columns and measures as below. 'Visual Control1' is used to display the data 364 days ago. 'Visual Control2' is used to display the data 12,11,10,9 months ago.

Year = YEAR(Calendar2[Date])
Month = MONTH(Calendar2[Date])
Visual Control1 = 
IF(
    SELECTEDVALUE('Calendar'[Date])-364 = SELECTEDVALUE('Table'[Date]),
    1,
    0
)
Visual Control2 = 
var _year = SELECTEDVALUE(Calendar2[Year])
var _month = SELECTEDVALUE(Calendar2[Month])
var _date1 = DATEADD('Calendar'[Date],-12,MONTH)
var _date2 = DATEADD('Calendar'[Date],-11,MONTH)
var _date3 = DATEADD('Calendar'[Date],-10,MONTH)
var _date4 = DATEADD('Calendar'[Date],-9,MONTH)
return
IF(
     (YEAR(_date1) = _year&&MONTH(_date1) = _month)||(YEAR(_date2) = _year&&MONTH(_date2) = _month)||(YEAR(_date3) = _year&&MONTH(_date3) = _month)||(YEAR(_date4) = _year&&MONTH(_date4) = _month),
     1,0

)

Finally you need to put the measure to the corresponding visual level filter to get the result.

Best Regards

Allan

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

[iqbal077]

Hi Allan,

Thanks so much for the assistance.  The first part of the solution works with the slicer,however, the second part where I have lot more challenges. The main issue is I have duplicates in my data set. At a given time, we take reading of on the business revenue in the database for the current month and future t + 5 months. Essentially the data set will look like this in the current month as well in the previous years.  The reading date and Revenue month are the two critcal pieces in this; So the business process requires us to show a snapshot of revenue sametime last year (date sensitive); 

Assuming 5/18/2019 is the comparative to this year, I want to show the reading of revenue for May 2019, June 2019 ,July 2019 & Aug 2019 as of the comparative date; 

Reading date | Revenue month | Rooms sold | Revenue |

5/14/2020      |     May               | 1200             | 100,000 |

5/14/2020      |     June               |  900              | 50,000   |

5/14/2020      |     July                 | 900              |  70,000  |

5/14/2020      |     Aug                | 700               | 60,000  |

5/15/2020      |     May               | 1290             | 101,000 |

5/15/2020      |     June               |  930              | 55,000   |

5/15/2020      |     July                 | 930              |  75,000  |

5/15/2020      |     Aug                | 710               | 62,000  |

5/16/2020      |     May               | 1200             | 100,000 |

5/16/2020      |     June               |  900              | 50,000   |

5/16/2020      |     July                 | 900              |  70,000  |

5/16/2020      |     Aug                | 700               | 60,000  |