Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jj_0511
Helper I
Helper I

data modeling / reporting problem

Hello,

I have a Dates table with 2 columns:

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

Daterelated 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:

datestoresales
2/1/2019  A45
2/1/2019  B95
1/5/2019  A26
1/5/2019  B11
1/4/2020  A82
1/4/2020  B12
1/31/2020  A10
1/31/2020  B66
1/29/2021  A71
1/29/2021  B27
1/2/2021  A39
1/2/2021  B25

 

The report I want is:

DateSales sales in related dates in 2019
1/4/2020  9437
1/2/2021  6437
1/31/2020  76140
1/29/2021  98140

 

How to do the modeling and produce the desired output?

 

Thanks.

1 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 🙂
 


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

View solution in original post

5 REPLIES 5
tackytechtom
Super User
Super User

Hi  @jj_0511,

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

tackytechtom_0-1683864671849.png

 

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 ) )
tackytechtom_3-1683864878152.png 
 
Let me know if this one worked for you 🙂


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

@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.

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 🙂
 


Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

jdbuchanan71
Super User
Super User

@jj_0511 
Join date to the sales table:jdbuchanan71_0-1683866353278.png

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] )

 

 

jdbuchanan71_0-1683864060369.png

 

 

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.