How would I do a dynamic visualization to show for Current and Next 12 Months Total Sales.

[mick22]

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

[v-yangliu-msft · Accepted solution]

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

[mick22]

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?

[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.

 

[mick22]

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.

[mick22]

I made some progress and things look good in Power Query Editor after reviewing your steps!  Thank you.  I do have a question though... I'm not getting month names across the top.

[Ashish_Mathur]

You are welcome.  In the Column labels, you should drag Year and Month name from the Calendar_dup table.

If my previous reply helped, please mark that reply as Answer.

[mick22]

I did that previously but they still didn't come through.

[Ashish_Mathur]

I cannot help unless i see your file.

[mick22]

Ashish - I apologize, I'll look closer! I truly appreciate your time in trying to help me out.

[amitchandak]

@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-Anal...

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