cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cjc322
Frequent Visitor

DATE RANGE SLICERS TO COMPARE NETSALES OF A DATE RANGE

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 

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

123.jpg

  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.
    234.jpg

View solution in original post

7 REPLIES 7
Baskar
Resident Rockstar
Resident Rockstar

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
Frequent Visitor

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

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

2.jpg

  1. Download this Power BI custom visual Timeline and import it. Select Date for the Time field.
    3.png
  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 )
    
    4.png
  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.
    5.png

@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 

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

123.jpg

  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.
    234.jpg

View solution in original post

@v-sihou-msft 

 

It's working. Thanks a lot

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors