Forum Discussion
How would I do a dynamic visualization to show for Current and Next 12 Months Total Sales.
I have a main table dataset (Table 2 below) that is imported in to PowerBI Desktop that contains a list of customers, agreements, the current monthly price and date range of how long that agreement goes.
I want to be able to create a dynamic visualization based on this data that shows in grid or matrix format the information like the Table 1 Below. This visualization would show the current and next 12 months total active agreements.
I do have a Date table with a relationship to Table2. The Date table contains a row for each day
Seeking help on how to use the data in Table 2 to achieve a visual like Table 1. Thoughts?
Desired Visualization (Table 1):
Table 2:
I do have a Date Table as well that contains each day:
| Date | Year | Year Month Name |
| 1/25/2021 | 2021 | January 2021 |
| 1/26/2021 | 2021 | January 2021 |
| 2/1/2021 | 2021 | February 2021 |
| x | x | x |
| x | x | x |
Hi mick22 ,
Here are the steps you can follow：
1. Create calculated table2.
Table2:
Table 2 =
CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Create the calculated column in Table2:
Year1 = VALUE( FORMAT('Table 2'[Date],"yyyymm"))Year2 = VALUE( FORMAT('Table 2'[Date],"yyyymm"))2. Create calculated table3.
Table3：
Table 3 = DISTINCT('Table 2'[Year2])3. Create measure.
Flag = var _1=CALCULATE(SUM('Table'[CurrentMonthlyPrice]),FILTER(ALL('Table 2'),'Table 2'[Year1]>=VALUE( FORMAT(MAX('Table'[StartDate]),"yyyymm"))&&MAX('Table 3'[Year2])<=VALUE( FORMAT(MAX('Table'[EndDate]),"yyyymm")))) return IF( MAX('Table 3'[Year2])<=MAX('Table 2'[Year1]),_1 ,0)Total_Incorrect = var _table=SUMMARIZE('Table 2','Table 2'[Year1],"_value",[Flag]) return IF(HASONEVALUE('Table 2'[Year1]),[Flag],SUMX(_table,[_value]))4. Result:
Refer to：
https://community.powerbi.com/t5/Desktop/Create-Lower-Triangular-Matrix/m-p/854339
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
11 Replies
- amitchandak
Super User
mick22 , You have to join it with two date tables. You have filter one of the table for 12 month
refer
Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Analysis/ba-p/1393410
In case you want to select one date and want filter more than that
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
- Ashish_Mathur
Hi,
I cannnot understand your result. First of all i think the year in the price start date column of Agree2 should be 2022 (not 2021). Please confirm that. Furthermore, how do you interpret the figure 250 at the intersection of June 2022 (row 2) and May 2022 (column1). I have done something based on my understanding. Download the PBI file from here.
Hope this helps.
Ashish - I like your output but the difference is your table called Data should have an Start Date and End Date. Values should be calculated for the Month (and projected out) IF they are between those Start and End Dates and displayed like you are doing.
I have updated my figures for my desired visualization to make things more clear.
- Ashish_Mathur
My Data table has a start and an end date. I have carried out some transformations on that table to get a single column dates. Please study the steps in the Query Editor.
Hi mick22 ,
Here are the steps you can follow：
1. Create calculated table2.
Table2:
Table 2 =
CALENDAR(MIN('Table'[StartDate]),MAX('Table'[EndDate]))
Create the calculated column in Table2:
Year1 = VALUE( FORMAT('Table 2'[Date],"yyyymm"))Year2 = VALUE( FORMAT('Table 2'[Date],"yyyymm"))2. Create calculated table3.
Table3：
Table 3 = DISTINCT('Table 2'[Year2])3. Create measure.
Flag = var _1=CALCULATE(SUM('Table'[CurrentMonthlyPrice]),FILTER(ALL('Table 2'),'Table 2'[Year1]>=VALUE( FORMAT(MAX('Table'[StartDate]),"yyyymm"))&&MAX('Table 3'[Year2])<=VALUE( FORMAT(MAX('Table'[EndDate]),"yyyymm")))) return IF( MAX('Table 3'[Year2])<=MAX('Table 2'[Year1]),_1 ,0)Total_Incorrect = var _table=SUMMARIZE('Table 2','Table 2'[Year1],"_value",[Flag]) return IF(HASONEVALUE('Table 2'[Year1]),[Flag],SUMX(_table,[_value]))4. Result:
Refer to：
https://community.powerbi.com/t5/Desktop/Create-Lower-Triangular-Matrix/m-p/854339
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Liu Yang - So I got this working a bit better and it pretty much looks like your example output. The only problem I'm having is that it's adding up CurrentMonthlyPrice for months that it should not based on YEAR1/YEAR2 being outside of the range of StartDate and EndDate. I did update my example in original post to clarify and post correct calculations. Any idea on why this might be happening?
