Calculate all to end of month for period

Benjamin · April 7, 2022, 1:15pm

Dear fellow Power BI enthusiasts!

Currently I am struggling with the following.

I have a measure which needs to calculate the amount all to date for the months end in a certain period. I’ve managed to create this for all the dates in the period selected, but I want it to only calculate for month’s end.

What It currently looks like:

But I want it to calculate & show only the months end:

The measure I use to calculate the All To Date numbers:

Amount LCY = 
VAR LasteSelectedDate =
    SELECTEDVALUE ( 'Reporting Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Accounts Receivable Sample Data'[AmountLCY] ),
        'Accounts Receivable Sample Data'[Date Transaction] <= LasteSelectedDate
    )
RETURN
    Result

The measure I use to calculate the numbers between the selected period:

Amount Outstanding LCY LTM = 
VAR LasteSelectedDate =
    SELECTEDVALUE ( 'Reporting Date'[Date] )
VAR NumberOfMonths = 12
VAR Dates =
    DATESINPERIOD (
        'Reporting Date'[End of Month],
        LasteSelectedDate,
        - NumberOfMonths,
        MONTH
    )
VAR Result =
    CALCULATE (
        [Amount LCY],
        REMOVEFILTERS ( 'Reporting Date' ),
        KEEPFILTERS ( Dates ),
        USERELATIONSHIP ( 'Reporting Date'[Date], '_reporting Date'[Date] )
    )
RETURN
    Result

I have the feeling that I am almost there, but I am eager to know what I am doing wrong. A sample report file has been attached.

Thanks in advance!

Accounts Receivable Sample Data.pbix (396.0 KB)

lbarron20 · April 7, 2022, 1:49pm

See if this helps

lbarron20 · April 7, 2022, 2:16pm

Another possibility is:

Amount Outstanding LCY LTM =
VAR LasteSelectedDate =
SELECTEDVALUE ( ‘Reporting Date’[Date] )
VAR NumberOfMonths = 12
VAR Dates =
LASTDATE(
‘Reporting Date’[End of Month]
)
VAR Result =
CALCULATE (
[Amount LCY],
REMOVEFILTERS ( ‘Reporting Date’ ),
KEEPFILTERS ( Dates ),

    USERELATIONSHIP ( 'Reporting Date'[Date], '_reporting Date'[Date] )
)

RETURN
Result

Benjamin · April 7, 2022, 2:36pm

Hello @lbarron20 !

Thanks for your replies, much appreciated.

With this post, as far as I understand, the person is trying to calculate the amount per end of the last end of month. This is not exactly what I want.

Perhaps it is not clear. I am trying to get the All To Date values, for the 12 End Of Months prior to the selected date. So if my date is set to 04-04-2022, I want to have the values:

All → 31-03-2022
All → 28-02-2022
All → 31-01-2022
…

So twelve data points with all the values up to that data point.

lbarron20:

Another possibility is:

Amount Outstanding LCY LTM =
VAR LasteSelectedDate =
SELECTEDVALUE ( ‘Reporting Date’[Date] )
VAR NumberOfMonths = 12
VAR Dates =
LASTDATE(
‘Reporting Date’[End of Month]
)
VAR Result =
CALCULATE (
[Amount LCY],
REMOVEFILTERS ( ‘Reporting Date’ ),
KEEPFILTERS ( Dates ),
    USERELATIONSHIP ( 'Reporting Date'[Date], '_reporting Date'[Date] )
)
RETURN
Result

If I alter the measure to be like this, I only get that months value:

I only want the end of months.

Thanks!

jafernandezpuga · April 10, 2022, 11:42am

Hello Benjamin,
You can modify the measure you created so that it displays only dates that are equal to the last day of the month:

‘Reporting Date’[Date] = ‘Reporting Date’[End of Month]

Amount Outstanding LCY LTM =
VAR LastSelectedDate =
SELECTEDVALUE ( ‘Reporting Date’[Date] )
VAR NumberOfMonths = 12
VAR Dates =
DATEINPERIOD (
‘Reporting Date’[End of Month],
LastSelectedDate,
-NumberOfMonths,
MONTH
)
VARResult =
CALCULATE (
[Amount LCY],
REMOVEFILTERS ( ‘Reporting Date’ ),
‘Reporting Date’[Date] = ‘Reporting Date’[End of Month], //*******
KEEPFILTERS(Dates),
USERELATIONSHIP ( ‘Reporting Date’[Date], ‘_reporting Date’[Date] )
)
RETURN
Result

Regards,

EnterpriseDNA · April 10, 2022, 8:33pm

Hello @Benjamin, just following up if the response above help you solve your inquiry?

We’ve noticed that no response has been received from you since a few days ago. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Benjamin · April 11, 2022, 8:06am

Hello @jafernandezpuga !

Thanks for your solution. This does indeed the trick. Do you perhaps know what I can use to have an inactive relationship between the Reporting Date table and Accounts Receivable Sample Date table? I forgot to mention this in my opening post and example.

Thanks again for your help!

EnterpriseDNA · April 14, 2022, 1:04am

Bumping this post for more visibility.

jafernandezpuga · April 15, 2022, 4:17pm

Hello Benjamin,
I have modified the data model so that the Reporting Date table is the date table connected to the Accounts Receivable Sample Data fact table and I have created the SlicerDate table, which is a copy of the latter and is disconnected, since it only will use for the selection of the date on which the calculations are based.

Además he modificado la medida Amount Outstanding LCY LTM como sigue:

Amount Outstanding LCY LTM = 
VAR LasteSelectedDate =
[LastSelectedDate] 
VAR _TableDates1 = SELECTCOLUMNS(
    FILTER(
      'Reporting Date',
      'Reporting Date'[Date] = 'Reporting Date'[End of Month]
    ),
    "Date",'Reporting Date'[Date]
) 
VAR _TableDates2 = DATESINPERIOD(
  'Reporting Date'[End of Month],
    LasteSelectedDate,
    - [NumberofMonths Value],
    MONTH
) 
VAR _TableDatesResult =
INTERSECT( _TableDates1, _TableDates2 ) 
VAR Result = CALCULATE( [Amount LCY], _TableDatesResult ) 

RETURN
Result

Regards,

Accounts Receivable Sample Data_JAFP.pbix (398.1 KB)

EnterpriseDNA · April 17, 2022, 8:45pm

Hello @Benjamin, just following up if the response above help you solve your inquiry?

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

EnterpriseDNA · April 19, 2022, 2:39am

Hello @Benjamin

Since it’s been a while since we got a response from you, in case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.