SAMEPERIODLASTYEAR with date filter

[RobbLewz]

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? 

[v-juanli-msft · Accepted solution]

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.

[AllisonKennedy]

This may sound like a silly question, but how do you define 'current month'. If they select a fiscal year in the past, do you only want to see that period and the previous period up to July?

I suspect you're looking for something aligned with YTD total for your first measure and then the second measure will update. There's a few ways you can get YTD total depending on what you want for 'current month'.

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

[amitchandak]

@RobbLewz , you need to use a date calendar in all such cases

refer:https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.