Month Based Slicer - Not Showing A Specific Year

[TheSAY]

Hello,

We use Daxko Operations as our membership software, and one report for membership information we need that exports an excel CSV can only be run on the first of every month.  For example, in order to get May 2019's unit and member numbers, we had to wait until June 1st 2019 to run it.

I am trying to create a report in Power BI using this data.  I have a folder called "Units" which has each year's CSV.

We don't want to see the numbers as cumulative, so a "month" slicer with a single selection is being used so we can see how many units and members we had per year of that given month.

The problem occurring however is that when I choose a month after the previous month, so June (6) for example (we won't have June's numbers until July 1st), no data is showing for 2019, as there is no June information yet.  Only when May (5) or earlier is selected, will 2019 show up.

Is there a way to show 2019 with May as the default, if June (6) or later is selected?

Here's the PBIX file: https://1drv.ms/u/s!Ar-qf5t6I8TOpCIoz5GHXYrq-Q24?e=KFbONZ

Here's the Unit folder: https://1drv.ms/u/s!Ar-qf5t6I8TOpCP0HYUycD0kjd06?e=Ut4ec4

[jdbuchanan71 · Accepted solution]

Hello @TheSAY ,

You will need to do a couple things to implement the fixes.

Mark the date table as a date table

Change relationship from the date table to the member table to be on 'Date'[Date] to 'Members'[Report Month]

Write the measures for Units and Members that compare the beginning of the selected month and the last report date for the year being reported and if the last report date is before the beginning of the month (you have picked June (6)) then, for that year, sum the data from the beginning of the last reported month through the end of the month selected: May 1st through June 31st. In earlier years it just calculates from the begging to the end of the selected month.

For 2019 it will show the May data if you select any month May or later but if you select an earlier month it will show the correct months data.

I have uploaded a copy of your PBIX with the changes made: https://www.dropbox.com/s/vtaahnhgpkguscr/NewMemUpdated.pbix?dl=0

You will see, for 2019 Jan - May the amount change but in June forward the May amount repeat.

Here are the two measures

Members = 
VAR StartDate = MIN(Dates[Date])
VAR EndDate = MAX(Dates[Date])
VAR LastReportDate = CALCULATE(MAX('Members'[Report Month]),ALL(Dates))
VAR ReportStart = MIN(StartDate,LastReportDate)

RETURN 
CALCULATE(
    SUM('Members'[Current Members]),
    DATESBETWEEN(
        Dates[Date],
        ReportStart,
        EndDate
    )
)

Units = 
VAR StartDate = MIN(Dates[Date])
VAR EndDate = MAX(Dates[Date])
VAR LastReportDate = CALCULATE(MAX('Members'[Report Month]),ALL(Dates))
VAR ReportStart = MIN(StartDate,LastReportDate)

RETURN 
CALCULATE(
    SUM('Members'[Current Units]),
    DATESBETWEEN(
        Dates[Date],
        ReportStart,
        EndDate
    )
)