Calculate dates less than Month/Year selected in slicer

[StaceyG]

I have a slicer for the MonthYear from my date table:

The following measure correctly calculates the data for only the MonthYear selected in the slicer:

I need a similar measure that calculates for all dates through the month/year selected.  The relationships is omnidirectional one-to-many from Date to Fact tables.

I have tried ALL(fact[date] at the end of each variable, instead of the current filter, but that still returned only the selected month's calculation.  Any ideas?

[StaceyG]

@PaulDBrown  

I've replaced the code for the Calendar table (= Date), verified the relationship is 1-to-many/single direction/inactive, and I'm still not getting correct results. For example, I have data for June and July. When I select June, the Historical totals are higher than the Historical totals when I select July. Also, when I select June the Historical and Monthly totals should be the same, as that is te first month with data.

I noticed your screenshot of the relationships, you show a "Cal Period" in Calendar Table, which is hidden in Period Filter Table. What is "Cal Period"?

[PaulDBrown]

You can Ignore the "Cal period" field. I created a calculated column for the equivalent field (I forget why!), so it's redundant:

It's hard to tell what the issue is, given that the measure basically changes the filter context for the measure from your Date table to your Calendar table.

After the change to the Calendar table (new code), is the filter acting as it should? ie. the periods filtered are as you would expect, even though the actual values are off?

Do you have the "Auto Time/date" option selected in the options for the file by any chance?

Also, what happens when you compute your [Total trx count] in a regular visual filtered by period? are the totals the same? I also see that the measure you referenced in your original post is different ([monthly performance]). So maybe the actual measure is affecting the result?

It would be great if you could share a sample PBIX file (you can change column headings to disguise the data if needed)

[PaulDBrown]

Ok, I would say the issue is the sorting of the MonthYear column. You need a Yearmonth column in both tables to sort the MonthYear column by. 

Edit: BTW, the easiest way to duplicate the 'Date' table is by creating a new table using:

Calendar = 'Date'

(so just substitute the code you have for the Calendar table with the above, but check the relationships if you do so)

Then the MonthYear column will be sorted by default. Finally make sure the visual is sorted by  MonthYear in ascending order.

[PaulDBrown]

Can you show me a screenshot of both the Date Table and the Calendar Table? Also in the SelPeriods VAR, are you sure the comparison is correct? it must be 

VAR SelPeriods = CALCULATETABLE(VALUES('Calendar'[MonthYear]),'Calendar'[MonthYear]<=CalD)

(not >= )

[StaceyG]

I confirmed the < is correct in VARSelperiods.  Here's a screenshot:

And the Calendar and Date tables:

Thanks,

Stacey

[PaulDBrown]

Here is one way.

1) Create a duplicate of your Date table. I've called mine "Period Filter Table". Join this table in a One-to-many inactive relationship (single direction) to the date table. The model should look like this:

Next create a measure along these lines:

Data by selected period =
VAR CalD =
    SELECTEDVALUE ( 'Calendar Table'[YearMonth] )
VAR SelPeriods =
    CALCULATETABLE (
        VALUES ( 'Period Filter Table'[YearMonth] ),
        'Period Filter Table'[YearMonth] <= CalD
    )
RETURN
    CALCULATE (
        [Sum of Sales],
        REMOVEFILTERS ( 'Calendar Table' ),
        KEEPFILTERS ( SelPeriods ),
        USERELATIONSHIP ( 'Calendar Table'[Date], 'Period Filter Table'[Date] )
    )

Create a slicer with the period field from the calendar table, and a visual with the axis/rows from the period table. 
You should get something like this:

[StaceyG]

@PaulDBrown I must be doing something wrong.  I have the relationships set the way you showed:

I created this measure:

HistoricTotalTrx =

VAR CalD = SELECTEDVALUE('Date'[MonthYear])

VAR SelPeriods = CALCULATETABLE(VALUES('Calendar'[MonthYear]),'Calendar'[MonthYear]<=CalD)

RETURN

CALCULATE([Total Trx Count], REMOVEFILTERS('Date'), KEEPFILTERS(SelPeriods), USERELATIONSHIP('Date'[Date], 'Calendar'[Date]))

But I'm getting strange results.  For example, when I select Jun 2021 from the slicer, I get data for Jun, Jul and Aug:

When I select Jul, I get data for Jul and Aug, no Jun.  FYI, my data begins in Jun.  What am I doing wrong?