Filtering a date column with a Measure (end of the month)

ezenunez · August 4, 2021, 4:08am

I am creating an ageing report, where all the data is provided at the end of each month in a table.

Unfortunately, I am struggling to filter the table base on measure [statement date] - It bring all the month base on my date Slicer.

I believe, my issue is around the context inside of my table. I understand, my measure is looking at each row and "re-calculate the of the month"rather than filter the base of my Statement date… my best guess

Here is one of the places I need to apply my filter…
ArMonth1 =
VAR result = CALCULATE(
SUM( AccountMonth[Month1] ),
FILTER(
AccountMonth,
AccountMonth[GLMonthEndDate] == [Statement Date]
)
)
RETURN
IF( ISBLANK( result ), 0, result )

Statement Date =
if( max(Dates[Date]) == [End of the Month], [End of the Month], [End of previous month])

This is pretty much what I am looking for:

Please see test 2 page. I can achieve this if I create a slicer with my end of the month…

Attach is my pbi

Thanks
Ezequiel
dna-eom.pbix (2.8 MB)

deltaselect · August 4, 2021, 12:31pm

Hi @ezenunez ,

Why would you not change your slicer and select GLMonthEndDate, which filters your source data (where you prepared your aging per month) ? A slicer for the different periods is always needed.
Maybe I missed something, but it gives your required aging report.
Looking forward to your reaction.

Kind regards, JW

P.S. If you would get rid of zero balances per bucket or only per total due, a small variation of your measure could be helpful:

Ar v3 StatementValues = 
Var CurrentItem = SELECTEDVALUE(ArStatementTemplate[Description])
Var Categorisation =
            SWITCH( TRUE(),
                CurrentItem = "Current", [ArCurrent],
                CurrentItem = "One Month", [ArMonth1],
                CurrentItem = "Two Months", [ArMonth2],
                CurrentItem = "Three Months +", [arThreeMonths+],
                CurrentItem = "Total Due", [arTotalDue],
            BLANK())
return
//alternative  IF(AND(CurrentItem = "Total Due", Categorisation = 0), BLANK(), Categorisation)  
IF(Categorisation <> 0, Categorisation, BLANK())

P.S. 2 A completely different method for aging reporting is explained by Sam McKay in two videos:

ezenunez · August 4, 2021, 9:56pm

Thanks a lot for your help JW,
I am creating a Summary report, where I show sales and other Receivables stuff. I may am creating another report only with the ageing report.

My intention is to show the latest “ageing report” base on the selection.

The logic I was trying to create is:
If the last day selected is the end of the month, look for that date accountmonth[GLMonthEndDate] otherwise use the previous end of the previous month.
Some how I can not make it work!

I will take your suggestion to get rid of the zeros!

Thanks

deltaselect · August 6, 2021, 8:31pm

Hi @ezenunez (version3)

What you want (EOM selected by a date slicer) is possible, please receive hereby a workout (base alternative) , and an alternative, see also attached PBIX :
dna-eom Base v8 def.pbix (2.8 MB)

Steps taken :

No changes made to the data model.

Base alternative (1)

Change the filtering from each of the existing 6 aging buckets measures, see below:

DS-ArCurrent = 
Var EOM = if( max(Dates[Date]) = EOMONTH(MAX(Dates[Date]),0), EOMONTH(MAX(Dates[Date]),0), EOMONTH(MAX(Dates[Date]),-1))
VAR result = CALCULATE( SUM( AccountMonth[MonthThis] ),
       Filter( AccountMonth, AccountMonth[GLMonthEndDate] = EOM ))
RETURN result

The crux is that for the filtering the EoMonth calculation has to be done inside the Aging Bucket Measure itself (so 6 times), instead of calling it with a outside measure like [Statement Date], which is not recognized in the context.
The Aging report can made after those measure adjustments.
The adjustments can be found in the measure group “AR Key Measures”

An alternative (2)

Add a calculated column to the “Dates” table with End of Month dates as required: an EOM is determined for every date, as a base for the use of a date slicer for the Month End Aging reporting.

Aging E O Month = IF(Dates[Date] = EOMONTH(Dates[Date],0), EOMONTH(Dates[Date],0),EOMONTH(Dates[Date],-1))
// if the date is EoM, then end of month current month; otherwise always end of month of the previous month (20/04/21 --> 31/03/21)

The aging report is filtered by a filter measure, which filters on the EOM based upon the Date slicer, (see below). This measure is added as filter at the Filter Pane for the Aging report

Aging Report Filter Measure = 
Var ChosenEOMonth = MAX(Dates[Aging E O Month]) // follows the date slicer ! EoMonth derived from calculated column "Aging E O Month"  from the Dates table
VAR SelectionAgingReport =
    CALCULATETABLE(
        VALUES( AccountMonth[GLMonthEndDate]),
        FILTER( ALLSELECTED( AccountMonth[GLMonthEndDate]), AccountMonth[GLMonthEndDate] = ChosenEOMonth))
VAR AgingReportFilter =  
    //IF( VALUES( AccountMonth[GLMonthEndDate] ) IN SelectionAgingReport, 1, 0 ) //as multiple EOM dates were to be presented in the reporting visual (not required)
    IF( SELECTEDVALUE( AccountMonth[GLMonthEndDate] ) IN SelectionAgingReport, 1, 0 )
RETURN AgingReportFilter

The measure is added to the filter pane of the Aging report

Note1: this measure is only needed to let the Date slicer work for required EOM Date. The Company-slicer works direct, without the need of a measure.
Note 2 : measure is setup as a variant on the solution for warehouse inventory, thanks @BrianJ !
Note 3 : to let the filter-measure work, “GLMonthEndDate” has to be included in the report-visual, because the filter-measure uses “IF( SELECTEDVALUE( AccountMonth[GLMonthEndDate] ) …”
Note 4: As the measure filters on the correct EOM-date, the measures for the age-buckets can be made simple, like " Current2 = SUM( AccountMonth[MonthThis] )"
Note 5: the new measures for the second alternative can be found in the Measure Group “DS alternative measures”

A possible report of the aging, using the date slicer : ( quite simular for both alternatives)

BrianJ · August 7, 2021, 2:56pm

@deltaselect ,

Fantastic. Love reading and working through your solutions. They’re like mini blog entries (or in some cases full blog entries).

Thanks so much for the time and thought you clearly put into these. I don’t see responses like this on any other forum.

Brian

ezenunez · August 8, 2021, 10:26pm

Thanks so much! This works extremely well!

deltaselect · August 8, 2021, 10:37pm

Hi @ezenunez ,
Glad I could help you out. It all appears that you have to do the EOM-calculation within your Aging buckets measures, see also above, my version 3.
Kind regards, JW

Keith · August 8, 2021, 10:41pm

Wow…i can’t believe all the incredible solutions within the forum