Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hello,
I am trying to create visualizations of NETSALES based on a FROMDATE in one Slicer and a TODATE in a second slicer.
I have attempted created DATE1 and DATE2 tables. And then buliding a caluclate NETSALES witha filter of FROMDATE and TODATE but I keep failing.
I am very new to DAX and am looking for someone to poke me in the right direction.
I basically need the measure that I would create in DATE1 and DATE2 to put into the calculation for the NETSALES. THAT current calculation looks like:
NetSalesSelectedDates = CALCULATE(sum(SAles[LineTotal],filter(sales,Sales[DAte]>= Date1Selection && Sales{Date} <= Date2Selection)))
However, I am having trouble creating a slicer that amounts to Date1Selection and DAte2Selection.
Any suggestions?
Thanks,
Chris
Solved! Go to Solution.
We can create one Start Date table and one End Date table for two slicers. Active the relationship between Start Date and Sales, but inactive the relationship between End Date and Sales as below.
NetSales = SUM ( Sales[LineTotal] ) NetSales_COMPARITIVE = CALCULATE ( SUM ( Sales[LineTotal] ), ALL ( 'Start Date' ), USERELATIONSHIP ( Sales[Date], 'End Date'[Date] ) )
%_INCREASE/DECREASE_Of_NETSALES = DIVIDE ( [NetSales_COMPARITIVE] - [NetSales], [NetSales] )
hi chris,
we cant do like this chris, if u create two column in table , its to wont work am sure.
i will give u one suggestion but am not sure try with two tables
1 for startdate
2 for end date
may it will help you
Hi Baskar,
I do currently have a DATE1 table for start date and DATE2 table for end date.
I am having trouble in creating the measure to include in the NETSALES calculation for each "selection" of a date from those tables.
My real goal is to create two visualizations. Visualization 1 will be NETSALES from DATE1 selection to DATE2 selection, second visualization will be NETSALES of comparative dates DATE3 and DATE4 with % increase / decrease from the first.
But before I get onto the second half of the equation I need to get the visualization to work with the slicer selection of NETSALES on DATE1 to DATE2.
Chris
In your canvas, if you select DATE1 for FROMDATE slicer and DATE2 for TODATE slicer at the same time, no NETSALES will be displayed. Because there is no NETSALES corresponding to DATE1 as well as DATE2. Each NETSALES always belongs to one date.
However, you can get the expected two results by using one custom Timeline slicer. Please refer to following steps:
I assume that your fact table is like below.
%_Increase/Decrease_From_The_First = VAR NETSALES_Of_Selected_FirstDay = LOOKUPVALUE ( Sales[LineTotal], Sales[Date], FIRSTDATE ( Sales[Date] ) ) VAR NETSALES_Of_Selected_LastDay = LOOKUPVALUE ( Sales[LineTotal], Sales[Date], LASTDATE ( Sales[Date] ) ) RETURN ( ( NETSALES_Of_Selected_LastDay - NETSALES_Of_Selected_FirstDay ) / NETSALES_Of_Selected_FirstDay )
Thank you so much for the detailed response. I think I have note explained what I am looking to create very well in the first place.
I want a visualization that displays NETSALES by DEPARTMENT of a DATE (or DATE RANGE).
I want a second visualizaton that displays NETSALES by DEPARTMENT of a COMPARITIVE DATE (or COMPARATIVE DATE RANGE).
I then want to show the % INCREASE / DECREASE of NETSALES from DATE vs COMPARITIVE DATE; total NETSALES and by DEPARTMENT if possible.
My fact table is actually be TRANSACTION so currently to calculate NETSALES I have this measure: NetSales = sum(Sales[LineTotal]).
I don't know if this information changes what you demonstrated below at all?
Chris
We can create one Start Date table and one End Date table for two slicers. Active the relationship between Start Date and Sales, but inactive the relationship between End Date and Sales as below.
NetSales = SUM ( Sales[LineTotal] ) NetSales_COMPARITIVE = CALCULATE ( SUM ( Sales[LineTotal] ), ALL ( 'Start Date' ), USERELATIONSHIP ( Sales[Date], 'End Date'[Date] ) )
%_INCREASE/DECREASE_Of_NETSALES = DIVIDE ( [NetSales_COMPARITIVE] - [NetSales], [NetSales] )
Thank you so much. That is exactly what I was trying to accomplish and will help me quite a bit.
I have some other questions unrelated but will start a new thread.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
93 | |
93 | |
84 | |
81 | |
49 |
User | Count |
---|---|
145 | |
142 | |
111 | |
71 | |
55 |