Returning the Last Value for Each Quarter of the Last Year to DIsplay in Table

[julesdude]

Hi,

Edit: Added some further examples:

I would like to create a table like this in my report that displays a Recurring Billing Amount value at a particular moment in time for a building:

In my data model, the table with all this information in looks like this - rows listed here are for just one Building Name, but there are many more as you can imagine - this one is just named AAA:

Building Name	Lease	Billing Category	Recurring Billing Frequency	Recurring Billing Beginning Date	Recurring Billing End Date	Recurring Billing Amount
AAA	2	Rent	Quarterly	01-May-21	30-Apr-22	936560.34
AAA	1	Rent	Quarterly	10-Jan-21	30-Sep-22	509492.18
AAA	1	Rent	Quarterly	01-Oct-20	30-Nov-20	0
AAA	2	Rent	Quarterly	01-May-20	30-Apr-21	919278
AAA	1	Rent	Quarterly	01-Dec-19	30-Sep-20	485275
AAA	1	Rent	Quarterly	01-Oct-19	30-Nov-19	0
AAA	1	Rent	Quarterly	01-Oct-19	30-Sep-20	505101.15
AAA	2	Rent	Quarterly	01-May-19	30-Apr-20	899643

It is showing the balance of a particular lease for a building between a beginning and end date window of time. Essentially what needs to happen here is quite straightforward to describe but difficult in produce. I've found I cannot achieve it simply by using the inbuilt Advanced Filter as shown:

This is because there are certain special conditions I need help with applying to make the data display correctly. What needs to happen:

1. The columns for the table in the report show years and quarters which should be prior to a measure name in my report called [as of date]. This is basically a date the user has selected from a drop down in the report. So the quarters displayed in the table must be quarters dating back a whole year from the as of date so that they include the as of date a year ago up to the seelcted as of date.

2. The table in the report should show the Recurring Billing Amount value at the time of the last day of the quarter shown. If the last day of that quarter is within the date range of a Recurring Billing Beginning Date and Recurring Billing End Date then the balance for that should be shown. It should not sum up any previous values or anything - just be a sum of all values (Recurring Billing Amount) that are valid for that moment of time at the end of the quarter. If there is a gap in the Billing Beginning Date and End Date which doesn't encompass the end day of a quarter, then the last known balance should be assumed. 
As an example with the above table, if I have an As Of Date of 31/12/2021 I would get results looking something like this:

Building Name	2021
	Q1	Q2	Q3	Q4
AAA	1428770.18	1,446,052.52	1,446,052.52	1,446,052.52

3. The Billing Category label has to be 'Rent' when using the Recurring Billing Amount values.

4. Leases should be grouped together when summed as part of the total for the Building

Appreciate any help on this.

Thank you.

[Anonymous]

Hi @julesdude,

It seems like a common date range that defined by multiple date field analysis requirement.

You can create a calendar table with unconnected date records and use it as column of matrix. Then you can write a measure formula to lookup raw table records based on current category field values and calendar date values.

Reference link:

Before You Post, Read This: “start date” and “end date” 

Regards,

Xiaoxin Sheng

[julesdude]

Hi @Anonymous 

Thanks. The reference link to the @Greg_Deckler article is definitely the direction to go. 

I think of the two examples for date ranges, the periodic billing one is probably more suitable.

Open Tickets - Microsoft Power BI Community

Periodic Billing - Microsoft Power BI Community

I tried to adapt the code to reference columns in my above table, but the totals I'm getting are incorrect and far greater in value than expected:

Total Amount = 
VAR tmpCalendar = ADDCOLUMNS(DateTable,"Month",MONTH([Date]),"Year2",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#")))
VAR tmpBilling = ADDCOLUMNS(Recurring_Billing,"MonthYearBegin",VALUE(YEAR([Recurring Billing Beginning Date]) & FORMAT(MONTH([Recurring Billing Beginning Date]),"0#")),
                                      "MonthYearEnd",VALUE(YEAR([Recurring Billing End Date]) & FORMAT(MONTH([Recurring Billing End Date]),"0#")))
VAR tmpTable = 
SELECTCOLUMNS(
    FILTER(
        GENERATE(
            tmpBilling,
            SUMMARIZE(tmpCalendar,[Year2],[Month],[MonthYear])
        ),
        [MonthYear] >= [MonthYearBegin] &&
        [MonthYear] <= [MonthYearEnd]
    ),
    "Customer",[Building Reference],
    "Year",[Year2],
    "Month",[Month],
    "Amount",[Recurring Billing Amount]
)
RETURN SUMX(tmpTable,[Amount])

 The original example in Gregg's article assumes a date table is already created. I do already have one that is imported in from a Dataflow. It is not connected to anything in the model, and has all the expected columns. It already had one called Year so substituted this reference in the above code to Year2 to avoid a conflict. 

I am not sure if I have changed the above references correctly, or if I need to add anything additional, so any further help would be great.

[Anonymous]

Hi @julesdude,

Here is a measure formula that I created based on your sample data, I also create a unconnected calendar use it as matrix column field with its year, quarter levels.

formula =
VAR currDate =
    MAX ( 'Custom'[Date] )
RETURN
    IF (
        //year filter
        YEAR ( currDate ) = YEAR ( MAX ( 'Table'[Recurring Billing End Date  ] ) ),
        CALCULATE (
            SUM ( 'Table'[Recurring Billing Amount] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                currDate >= [Recurring Billing Beginning Date  ]
                    && currDate <= [Recurring Billing End Date  ]
            )
        )
    )

Regards,

Xiaoxin Sheng

[julesdude]

Hi @Anonymous 

Many thanks for your help so far. Unfortunately it is not bringing me the results I need.

I tried another dataset:

Building Name	Lease Reference	Billing Category	Recurring Billing Frequency	Recurring Billing Beginning Date	Recurring Billing End Date	Recurring Billing Amount
AAA	111	Rent	Monthly	01-Jan-23	01-Jan-36	1,858,598
AAA	111	Rent	Monthly	01-Jan-22	01-Jan-36	1,804,464
AAA	111	Rent	Monthly	01-Jan-21	01-Jan-36	1,770,000
BBB	111	Rent	Monthly	27-Mar-19	26-Mar-26	105,489
CCC	111	Rent	Monthly	21-May-18	21-May-23	21,495
CCC	111	Rent	Monthly	21-May-18	21-May-23	3,574
DDD	111	Rent	Monthly	01-Jan-17	31-Dec-23	1,059,717
EEE	111	Rent	Monthly	01-Jul-13	01-Jan-23	55,341
FFF	111	Rent	Quarterly	20-Apr-09	14-May-21	1,456
GGG	222	Rent	Monthly	01-Jan-23	01-Jan-36	1,153,906
GGG	222	Rent	Monthly	01-Jan-22	01-Jan-36	1,120,297
GGG	222	Rent	Monthly	01-Jan-21	01-Jan-36	1,098,900
BBB	222	Rent	Monthly	10-Mar-19	09-Mar-22	152,307
DDD	222	Rent	Monthly	01-Nov-17	31-Oct-22	38,588
FFF	222	Rent	Quarterly	24-Sep-10	28-Feb-22	1,510
HHH	333	Rent	Quarterly	01-Oct-09	30-Sep-19	55,738
HHH	333	Rent	Quarterly	10-Jan-09	31-Dec-22	57,287

I get the below which is incorrect (when As Of Date selected in the date picker is 31/12/2022):

The values here are wrong but also not every row is displayed. For example, I would expect to see something like this for the first row (Building Name AAA) because it has dates that fall either side of the as of date of 31/12/2022:

If my as of date selected had been 17/04/2023, then from 2023, AAA > 111 would have a recurring billing total of 5,433,062
I've attached an example workbook with your method applied using the dateset above.

Any further help really appreciated.

Ultimately what I am trying to get here is just the last year's worth of billing (so as of date back to a year ago) and having inbetween the quarterly columns the difference in value +/- in %. between them.