DAX - Same period comparsion

[Anonymous]

Hi guys,

I ran into a puzzle I can't solve with my DAX knowledge.

I will try to shortly explain what I'm trying to achieve and then show you what I was able to do so far.

Let's assume my current fact in the data warehouse looks like this:

StoreID	DateID	Qty
1	1.1.2019	10
1	1.2.2019	10
1	1.3.2019	10
2	1.2.2019	15
2	1.3.2019	15

As you can see, the 1st store began selling their products in Jan-2019, however, the 2nd store began in Feb-2019.
Because I only want to compare the same periods, I need to filter this fact this way:

StoreID	DateID	Qty
1	1.1.2019	10
1	1.2.2019	10
1	1.3.2019	10
2	1.2.2019	15
2	1.3.2019	15

If I put this into SQL language, this is basically everything I want to do:

DECLARE @MaxDate DATE = '';
SELECT
	@MaxDate = MAX(MinDateID)
FROM
	(
		SELECT
			MIN(DateID) AS MinDateID,
			StoreID
		FROM
			FactSalesInvoice
		WHERE 
			StoreID IN (1, 2)
		GROUP BY
			StoreID
	)

SELECT
	*
FROM
	FactSalesInvoice
WHERE
	DateID > @MaxDate
AND StoreID IN (1, 2)

The users would then be able to compare store data for the periods that all chosen stores were already established.

So, I've tried creating a Min Date per Store table like this:

SUMMARIZE('Sales Invoice','Sales Invoice'[StoreID], "MinDate", MIN('Sales Invoice'[DateID]))

And then - many variations of it, but none of them worked the way I wanted it to - a measure:

SamePeriodSale = 
VAR MaxSalesInvoiceDate = MAX(MinDateSalesInvoice[MinDate])
RETURN
CALCULATE (
    'Sales Invoice'[Sales Invoice Value],
    FILTER('Date', 'Date'[DateID] >= MaxSalesInvoiceDate)
)

Basically, the one thing I really can't understand, is how to get a variable that would calculate 1.2.2019 for all of the rows in the table. Right now, I'm able to get 1.1.2019 for the 1st store, and 1.2.2019 for the 2nd store.

What is the way to get on top of this thing? I'm really not familiar with DAX yet, but I'm hoping someone can help me. 

Thanks in advance.

[v-lili6-msft]

hi, @Anonymous 

First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, based on my research, you could create these two measures

Date of the public = CALCULATE(CONCATENATEX(VALUES('Table'[DateID]),[DateID],,CALCULATE(MAX('Table'[DateID])),ASC),FILTER(ALLSELECTED('Table'),'Table'[DateID]<=MAX('Table'[DateID])))

filterstatus = IF(CALCULATE( COUNTAX('Table',[Date of the public]),ALLSELECTED('Table'[StoreID]))=CALCULATE(DISTINCTCOUNT('Table'[StoreID]),ALLSELECTED('Table')),1,2)

Then drag measure[filterstatus] into visual level filter of table visual and set filter is "1"

Result:

and here is pbix file, please try it.

Best Regards,

Lin

[Anonymous]

Hi,

@PattemManohar - your solution does not take filters into consideration. If I selected one store ID, it still gets MAX from all the stores.

@v-lili6-msft,

I've tested your solution and it works the way I'd want it. But if I test it on our data, it immediately runs out of memory even on a small dataset (1000s of records), but in production, we're gonna have billions of records. 

So this is a no-go. Is there a better way?

 
And also, as you've stated, the measures aren't affected by slicers.

[v-lili6-msft]

hi, @Anonymous 

And also, as you've stated, the measures aren't affected by slicers.

First, you have a wrong understanding of it.

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

Second, For the logic of this measure will iterate over each row of data multiple times and then filter data in the memory, So it will take up a lot of memory. Maybe we need to optimize the formula.

could you share us more sample data (small dataset (1000s of records)) for us as a test, You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading

Best Regards,

Lin

[PattemManohar]

@Anonymous  Please try this as a New Table.

Test232Out = 
VAR _MinDate = MAXX(SELECTCOLUMNS(SUMMARIZE(Test232Filter,Test232Filter[StoreID],"MinVal",MIN(Test232Filter[DateID])),"Minval",[MinVal]),[Minval])
RETURN FILTER(Test232Filter,Test232Filter[DateID]>=_MinDate)