Attempting to calculate a total based off end of month date

[Anonymous]

Hello, I currently have a measure that draws from multiple tables to satisfy an "Active Customer Count" monthly. The measure is as follows.

Customers By Area =
CALCULATE (
SUM ( FactActiveCust[Active_Count] ),
ALL ( DimRegions ),
FILTER (
DimRegions,
DimRegions[SubdivisionCode]
= SELECTEDVALUE ( 'Account Master GL'[MainRegCode], BLANK () )
),
ENDOFMONTH ( DimDates[Date] )
)

It might be a little confusing, there were some unconnected queries and so there were multiple steps. It might not be relevant to my question, but I figured I would include it so one of you can offer a solution given the current format.

There is currently a "Date Calculated" column, where in January (e.g) there will be a count during 1/1, 1/2, 1/4, 1/7, 1/12.......1/31, for example. It is different each month. However, as long as there is an end of month date, the entire customer count is summarized neatly. Over the past few years, there are a couple of months where the end date falls a few days short. For example, September 2019's last recorded date of count is 9/27. As a result, these monthly counts are showing up as blanks.



Ideally: I am looking to modify my measure (if possible) where it counts some sort of MAXDATE, LASTDATE (of month), etc. that ignores the final date of the month and looks towards the last recorded date of the month to summarize.

However: If all else fails, I know the numbers that I need, in total it's about 20 locations x 4 months where this probelm occurs, and I am okay with manually entering the data if there is some sort of way. It shouldn't take more than 15 minutes and I would be very happy if this could be resolved by the end of the day.

Thanks!!!

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Please also use the following measure:

Customers By Area =
VAR a =
    SELECTEDVALUE ( 'Account Master GL'[MainRegCode], BLANK () )
VAR b =
    MAX ( DimRegions[Date Calculated] )
RETURN
    CALCULATE (
        SUM ( FactActiveCust[Active_Count] ),
        ALL ( DimRegions ),
        DimRegions[SubdivisionCode] = a,
        DimRegions[Date Calculated] = b
    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[lbendlin]

Do not use SELECTEDVALUE inside a CALCULATE.  Assign a variable, and then use that.

You are calculating under a filter context already so you can simplify your code.

Customers By Area =
var a = SELECTEDVALUE ( 'Account Master GL'[MainRegCode], BLANK () )
return CALCULATE (
SUM ( FactActiveCust[Active_Count] ),
ALL ( DimRegions ),
DimRegions[SubdivisionCode] = a,
ENDOFMONTH ( DimDates[Date] )
)