calculation based on last 12 months comparison with last to last 12 month based on selected date

[NehaSha]

Hi All,

Need help for showing chart graph and table  for last 12 month comparison with last to last 12 month comparison  based on timeline slicer selected date

Lets say date selected 3-31-2018 ---it is end date or the last date of that month –so it will be considered as end date

But if someone selected 3-12-2018 ----than end date will be considered as the previous month last date =2-28-2018

--------------Table structure -----------------------------

LocationID           Date                      SalesIn

1                              2019-05-23          12

1                              …..

1                              2019-05-01          2

1                             2019-04-01          10

1                              2019-04-02          14

1                              …..

1                              2019-04-30          20

1                              ….

1                              2014-01-01          16

2                              2019-05-23          12

2                              ….

2                              2014-01-01          9

3                              2019-05-23          15

3                              ….

3                              2014-01-01          8

 calculation based on the date range (start date will always be first of the month :

DateRange with Same Period Last Year for Calculation  DateRange with Same Period Last to Last Year Calculation

Month1 represent (2019-04-01 to 2019-04-30     )                              (Month1 2018-04-01 to 2018-04-30)                       

Month2 represent (2019-03-01 to 2019-03-31    )                              (Month2  2018-03-01 to 2018-03-31)                                                                                      

Month3 represent (2019-02-01 to 2019-02-28     )                              (Month3  2018-02-01 to 2018-02-28)                      

Till

Month10                             2018-07-01 to 2019-07-31                             Month10 2017-07-01 to 2017-07-31                        

Month11                             2018-06-01 to 2018-06-30                             Month11 2017-06-01 to 2017-06-30                        

Month12                             2018-05-01 to 2018-05-31                             Month12 2017-05-01 to 2017-05-31                        

How to get below mentioned table in Result

MMMYY              MMMYY Last Year   SalesInMMMYY  SalesInMMMYY last year      NumMMMYYSorting  

Apr 19                                   Apr 18                   ?                              ?                                              1

Mar 19                                  Mar 18                  ?                              ?                                              2

Feb 19                                   Feb 18                   ?                              ?                                              3

….

Jul 18                                     Jul 17                     ?                              ?                                              10

Jun 18                                    Jun 17                    ?                              ?                                              11

May 18                                 May 17                 ?                              ?                                              12

SQL query has full load table which contain daily data till yesterday date.

How to show MMMYY , MMMYYLY,Num column in table and MMMYY in graph on axis, and  sorting based on Year, month desc or desc ?

How to get the sum of the matrix for each month 1 to month 12 and then month13 to month 24 ---to show the in the graph and table in parallel based on selected date ?

Location ID Slicer =All

Date Timeline Slicer = 5-5-2019 –if it is not the last date of the month than take previous month as end date

-----by default in calculation start date will always be first of the month

---One set of 12 month data with MMMYY

SalesIn Calculation = Sum (SalesIns) where date between  --2019-04-01 to 2019-04-30

                                      = Sum (SalesIns) where date between  -- till

                                      = Sum (SalesIns) where date between  --2018-05-01 to 2018-05-31

-----another set of 12 Month data with MMMYY Last Year

SalesIn Calculation = Sum (SalesIns) where date between  --2018-04-01 to 2018-04-30

                                      = Sum (SalesIns) where date between  -- till

                                      = Sum (SalesIns) where date between  --2017-05-01 to 2017-05-31

How to get the graph like below mentioned  in graph result

--------------------------MMMYY on axis

Thanks in advance!!

[NehaSha]

Hi @v-xicai ,

I have prepared the mockup pbix but not able to see any option to attach.  Do you have any idea how can I share the pbix file. 

Thanks 

Neha

[v-xicai]

Hi @NehaSha ,

 You can find Onedrive here, upload sample pbix to OneDrive or other share plateform, then post the link in this forum case.

Best Regards,

Amy

[NehaSha]

Hi @v-xicai ,

Thanks for your reply! Please see the below mentioned link:

"https://simplyselfstorage-my.sharepoint.com/:u:/g/personal/nsharma_simplyss_com/EVuQdd_ySmNKl77DITZ...

Please let me know in case of any questions.

Thanks,

Neha

[NehaSha]

Hi @v-xicai ,

Thanks for your reply! Please see the below mentioned link:

https://simplyselfstorage-my.sharepoint.com/:u:/g/personal/nsharma_simplyss_com/EVuQdd_ySmNKl77DITZ9...

Please let me know in case of any questions.

Thanks,

Neha

[v-xicai]

Hi @NehaSha ,

Could you please upload sample pbix to OneDrive or other share plateform and post the link here for further analysis ? Do mask sensitive data before uploading.

Best Regards,
Amy