Latest Enterprise DNA Initiatives

Measures with Filters

New to DAX mechanics, I would like to do something simple:

I have included data and pbix file.

I have a matrix and need 3 measures:

  1. Total Sales (This one is simple and I got it)
  2. LYSP Sales (Last year same period sales, attention I only have periods as info)
  3. YTD Sales (From beginning of the year to current period)

I would like to figure out DAX logic for points 2 and 3. And I would like to use the measures in the MAtrix and respective labels.

I think I need a measure that includes a CALCULATE and a FILTER, but Im not completely sure. Most examples in web include a calendar table, but I need to do it with Periods table. Looks simple but …

Sample_Sales_Period.pbix (56.1 KB)
Simple_Sales_Sample.xlsx (14.1 KB)

As an example, if I select period 08-2021, then the measure should return something like:

SPLY Sales = CALCULATE([Total Sales],FILTER(Sales,Sales[Period] = 202008))

of course the period 202008 would have to be derived from Current period (202108) - 100000 => 202008 (or something similar)

Hi @JR_PBI ,

Hope all is well.

As there are no fields with a date type in the attached, one option would be to assign a date to each period e.g. 06-2020 = 01/06/2020 (Column From Examples in Power Query works perfectly). You could then link the Period Table to the Sales Table and apply the standard Time Intelligence calculations.

Please see screenshot below. Hope this helps.

Screenshots below of additional fields if needed:


Thanks R123_Fin,

I see the technique with the extra date.

1 Like