Sum Last Full Month MTD and YTD based on current date

[gsmith_eqd]

Hello,

I'm trying to build a report that has cards showing A) last months total sales, B) last month previous year total sales, C) YTD sales up to present date, and D) previous YTD sales up to present date. So for example, if the date was March 14, 2023, A) would display total sales for February 2023, B) would display sales for February 2022, C) would display YTD sales up to March 14, 2023, and D) would display previous YTD Sales up to March 14, 2022.

So far, these are some of the measures I have created on the InvoiceLine table:

Total Invoicing = SUM(InvoiceLine[InvoiceLineAmount])

InvoiceYTD = TOTALYTD([Total Invoicing], 'calendar'[Date].[Date])

InvoicePYYTD = TOTALYTD([Total Invoicing], SAMEPERIODLASTYEAR('calendar'[Date].[Date]))

InvoiceMTD = TOTALMTD([Total Invoicing], 'calendar'[Date].[Date])

InvoicePYMTD = TOTALMTD([Total Invoicing], SAMEPERIODLASTYEAR('calendar'[Date].[Date]))

I have a calendar table linked to InvoiceLine with the columns: Date, Day, Month, Monthnum, QTR, Weeknum, and Year.

In these samples I created, the data was updated on 1/17/2023, so I've selected the periods on the tables that I would like to display so the cards show the YTD invoicing up to 1/17 and the MTD invoicing for December 2022 and 2021. However, the behavior I'm hoping to achieve is that I can have these cards all on one page without the need to focus on the period in a table as it is the hope that users of this report will be able to display this information at a glance without having to filter the information either with the focus tool or filters.

I was able to create a measure for C) above that reads like this:

ThisYearToDayInvoicing = TOTALYTD([Total Invoicing], 'calendar'[Date].[Date], 'calendar'[Date] <= TODAY() && 'calendar'[Year] = YEAR(today()))

but when I when I try to create the same measure for last year, it comes up blank:

LastYearToDayInvoicing = TOTALYTD([Total Invoicing], SAMEPERIODLASTYEAR('calendar'[Date].[Date]), 'calendar'[Date] <= TODAY() && 'calendar'[Year] = YEAR(today()))

This is the formula I tried for the MTD measure but did not return a value:

LastMTD = TOTALMTD([Total Invoicing], 'calendar'[Date].[Date], ('calendar'[Monthnum] = 'calendar'[Monthnum] - 1 && 'calendar'[Monthnum] <> 1) || 'calendar'[Monthnum] = 12)

Can I accomplish this with DAX formulas or are there other considerations I need to make about which tools will be needed to accomplish this?

Thanks,

Gavin

[Greg_Deckler · Accepted solution]

@gsmith_eqd You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...