data modeling / reporting problem

[jj_0511]

Hello,

I have a Dates table with 2 columns:

Values in Date column is unique, values in "related date in 2019" is not unique:

Date	related date in 2019
1/4/2020	1/5/2019
1/2/2021	1/5/2019
1/31/2020	2/1/2019
1/29/2021	2/1/2019

I have another table Sales:

date	store	sales
2/1/2019	A	45
2/1/2019	B	95
1/5/2019	A	26
1/5/2019	B	11
1/4/2020	A	82
1/4/2020	B	12
1/31/2020	A	10
1/31/2020	B	66
1/29/2021	A	71
1/29/2021	B	27
1/2/2021	A	39
1/2/2021	B	25

The report I want is:

Date	Sales	sales in related dates in 2019
1/4/2020	94	37
1/2/2021	64	37
1/31/2020	76	140
1/29/2021	98	140

How to do the modeling and produce the desired output?

Thanks.

[tackytechtom]

Hi  @jj_0511,

I'd probably just do one normal conenction from sales to date:

Next, I'd create a normal Sales measure:

SalesMeasure = SUM ('Table'[sales] )

And finally, the SalesRelDates2019 measure:

SalesRelDates2019 =

VAR CorrespondingDate2019 = SELECTEDVALUE ( 'Date'[related date in 2019] )

RETURN

CALCULATE ( [SalesMeasure], ALL ( 'Table'),  'Table'[Date] = ( CorrespondingDate2019 ) )

 

Let me know if this one worked for you 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jj_0511]

@tackytechtom Thanks.

In your code: CALCULATE ( [SalesMeasure], ALL ( 'Table'),  'Table'[Date] = ( CorrespondingDate2019 )

what's purpose of ALL ( 'Table')?

why the following code does not work:

CALCULATE ( [SalesMeasure], ALL ( 'Table'[Date]),  'Table'[Date] = ( CorrespondingDate2019 )

Thanks.

[tackytechtom · Accepted solution]

Hi  @jj_0511 ,

I just removed the filter for the whole table (with "ALL ( table )" ) which assumably even removes the filter from the Date table. 

This one here is probably the most correct one, but honestly, it depends a bit on how you wanna be using your measure.

SalesRelDates2019 = 
VAR CorrespondingDate2019 = SELECTEDVALUE ( 'Date'[related date in 2019] ) 
RETURN
CALCULATE ( [SalesMeasure], ALL ( 'Date'[Date] ),  'Table'[Date] = ( CorrespondingDate2019 ) )

Here, we remove the current filter context just for the Date column of the date table. That column was also the one we were using in the visual itself meaning it was the one applying the filter onto the calculations. So if we take that one away (with ALL 'Date[Date]) and then use the date column again to assign a new filter constraint ('Table'[Date]=CorrespondingDate2019), we achieve what we want. ALL ( 'Table'[Date] ), would only remove the filter context from the date column of the 'table' which was not used in the visual in the first place. That is why taking away that filter does not have any effect which leads to blank rows because you are using two date filters at the same time: One from the visual as well as the one in the formular.

Hope this help 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom

[jdbuchanan71]

@jj_0511 
Join date to the sales table:

I needed an intermediate measure for 2019 sales to use to get the total correct so there are 2.

The first one moves the 2019 related date to the sales table and calculates the amount.

Sales_2019_intermediate = 
CALCULATE (
    SUM ( Sales[sales] ),
    ALL ( Dates ),
    TREATAS ( VALUES ( Dates[related date in 2019] ), Sales[date] )
)

The second one uses the first and calcualtes the total correctly.

Sales in related dates in 2019 = SUMX ( DISTINCT ( Dates[Date] ), [Sales_2019_intermediate] )

[jj_0511]

@jdbuchanan71 Thanks for the anwser. This is a bit advanced for me. I'll get back to you in case of questions.