DATE RANGE SLICERS TO COMPARE NETSALES OF A DATE RANGE

[cjc322]

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 

[Baskar]

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 

[cjc322]

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

[v-sihou-msft]

@cjc322

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.

1. Download this Power BI custom visual Timeline and import it. Select Date for the Time field.
   
2. Create a measure for increase/decrease percentage of NETSALES from the first selected date. Set the format to “Percentage”.
   

   %_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 )
   

   
3. Drag a table into canvas. Select LineTotal and %_Increase/Decrease_From_The_First for the Values field as below.
   The LineTotal will show the sum from 4/29/2016 to 5/6/2016. The %_Increase/Decrease will show increase/decrease percentage of NETSALES between 4/29/2016 and 5/6/2016.
   

[cjc322]

@v-sihou-msft

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 

[v-sihou-msft · Accepted solution]

@cjc322

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.

1. Create two measures for NETSALES of a DATE (or DATE RANGE) and NETSALES of a COMPARITIVE DATE (or COMPARATIVE DATE RANGE).
   

   NetSales = 
   SUM ( Sales[LineTotal] )
   
   NetSales_COMPARITIVE = 
   CALCULATE (
       SUM ( Sales[LineTotal] ),
       ALL ( 'Start Date' ),
       USERELATIONSHIP ( Sales[Date], 'End Date'[Date] )
   )
   

2. Create a measure to show the % INCREASE / DECREASE of NETSALES from DATE vs COMPARITIVE DATE.
   

   %_INCREASE/DECREASE_Of_NETSALES = 
   DIVIDE ( [NetSales_COMPARITIVE] - [NetSales], [NetSales] )
   

3. Drag slicers and tables into canvas.
   

[harib]

@v-sihou-msft 

It's working. Thanks a lot

[cjc322]

@v-sihou-msft

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.