Problem with choosing noncontiguous dates

[monika_eliza]

Hello,

I have a problem with using a date slicer: when I am choosing multiple dates which are noncontiguous (e.g. March and May instead of March, April and May) my matrix visual crashes.

In my dataset I have two tables: the first one is Calendar table I created myself (I am not using CALENDARAUTO() nor CALENDAR() functions). My dates are noncontiguous - I start from the last day of Jul '21 and end on the last day of Jun '23, so each month has only one date (the last day of the month). The table contains unique values only. My second table (PowerBI_input) contains the date (also only the last day of the month) and sales columns. I connected those two tables with a relationship (one to many, single direction) from Calendar table to PowerBI_input table where the key is date.

To create my matrix visual, I created the following measures:

Sales m. = SUM(PowerBI_input[Sales])

Sales Sameperiodlastyear m. = CALCULATE((SUM(PowerBI_input[Sales])), SAMEPERIODLASTYEAR(('Calendar'[Date])))

My matrix shows years and months in rows and sales and sales sameperiodlastyear in columns.

I already tried creating a new key which is not date, adding CALENDARAUTO() function as a third table but I couldn't find a solution.

I would be very grateful for help!

[monika_eliza · Accepted solution]

To anyone who would have the same problem in the future, I found a solution.

Instead of:

Sales Sameperiodlastyear m. = CALCULATE((SUM(PowerBI_input[Sales])), SAMEPERIODLASTYEAR(('Calendar'[Date])))

use:

Sales YA = CALCULATE(SUM(PowerBI_input[Sales]), PARALLELPERIOD('Calendar'[Date], -12, MONTH))

[monika_eliza]

Hi @Anonymous,

In the link I am sharing the sample model with you: https://drive.google.com/file/d/1B4QtamNtxrCERpsqa8ltAszLDHY5ralI/view?usp=sharing 

When I choose noncontiguous months, my matrix shows me this message:

Regards,

Monika

[Anonymous]

Hi @monika_eliza ,

Please share some sample data so that we could test the formula.

Best Regards,

Jay