Forum Discussion

RobbLewz's avatar
RobbLewz
Helper II
6 years ago
Solved

SAMEPERIODLASTYEAR with date filter

I have a view (bi.vwInvoicedTotals) with a date column as in 01/01/2019 would be 20190101

I have a date dimension with a date column as int, then the month number, full date, month name etc

These are joing on the date column as int.

 

In my bi report I have a measure "selected year"

Selected Year = SUM('bi vwInvoicedTotals'[TotalInGBP]) - this is filtered from a slicer where the users selects a fiscal year.
 

I have anohter measure then, "Year Prior" which has always been. = Year Prior = CALCULATE([Selected Year], SAMEPERIODLASTYEAR('vwDates'[FullDate]))

 

I want to filter Year Prior to only show the total up to the current month.  Where are now it shows the full 12 months of the prior year.  I have tried this and a few other variations but not getting any luck.

 

 

This has no change at all

Year Prior =
    CALCULATE (
        [Selected Year],
        FILTER ( ALL (vwDates), vwDates[MonthOfYear] <= MONTH(TODAY()) ),
         SAMEPERIODLASTYEAR(vwDates[FullDate])
    )

This just shows 0
Year Prior =
    CALCULATE (
        [Selected Year],
        FILTER ( vwDates, vwDates[MonthOfYear] <= MONTH(TODAY()) ),
         SAMEPERIODLASTYEAR(vwDates[FullDate])
    )

 

 

Is this possible to do? 

Need Help
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi RobbLewz 

    Create a measure

    Measure 2 =
CALCULATE (
    SUM ( 'Table 3'[sale] ),
    FILTER (
        'date',
        'date'[Date]
            <= EOMONTH (
                DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) ),
                0
            )
            && SAMEPERIODLASTYEAR ( 'date'[Date] )
    )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies