Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!
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
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
Hi @v-xicai ,
Thanks for your reply! Please see the below mentioned link:
Please let me know in case of any questions.
Thanks,
Neha
Hi @v-xicai ,
Thanks for your reply! Please see the below mentioned link:
Please let me know in case of any questions.
Thanks,
Neha
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |