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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

@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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.