Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Total Sales from 2 different dates

Hello all. I hope everyone's ok. I believe i got a challenge for you all. I need to see the Sales for 2 distinct periodes I have this visual that shows me the total sales by Product and by filtere...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi pedroccamaraDBI 

    You need to create 2 date tables, and use them to create date slicers. I create a sample file for your reference.

    Sample Data

    Step 1: create two date tables and establish an inactive relationship between them

    Calendar1 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))
    Calendar2 = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

    .

    Step2: create the measures bellow

     

     

    Sales_Period1 = SUM('Table'[Net Sales])
    Sales_Period2 =
VAR _period2 =
    CALCULATETABLE (
        VALUES ( Calendar1[Date] ),
        REMOVEFILTERS ( Calendar1 ),
        USERELATIONSHIP ( Calendar1[Date], Calendar2[Date] )
    )
RETURN
    CALCULATE ( [Sales_Period1], _period2 )

    .

    FYI:

    Active vs inactive relationship guidance

    USERELATIONSHIP()

    REMOVEFILTERS()

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago

Hello amitchandak 
I don't think it's working. Maybe i've done something wrong. The 2nd measure should be over the sales table, right? This is what i've done

Net Sales 2nd Date =
VAR _MaxDate = MAXX( ALLSELECTED('Tab Sales'[Request Date]), 'Tab Sales'[Request Date])
VAR _MinDate = MINX( ALLSELECTED('Tab Sales'[Request Date]), 'Tab Sales'[Request Date])
RETURN
CALCULATE( [Net Sales], FILTER (ALL('Tab Sales'), 'Tab Sales'[Request Date] <= _MaxDate && 'Tab Sales'[Request Date] >= _MinDate ))

As you can see the results aren't correct.


Do you know what went wrong here?
Thanks a lot Amit

 

  • pedroccamaraDBI's avatar
    pedroccamaraDBI
    Icon for Post Partisan rankPost Partisan
    4 years ago

    What do you mean Amit? The CALCULATE expression is not over my sales table? Is it over my Dates table or over a new one?