Problem with choosing noncontiguous dates

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!

Need Help

monika_eliza
3 years ago
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))

Forum Discussion

Problem with choosing noncontiguous dates

3 Replies

Related content

Logic problem?

Choosing connection types for lakehouse

Slider problems

Problems in UK South

FILTERING PROBLEM