Total Sales from 2 different dates

[pedroccamaraDBI]

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 filtered date. Something like this.

My question is simple: what do i have to do to have this 2nd periode measure on the same visual?
Thank you all

Best regards

[v-xiaotang]

Hi @pedroccamaraDBI 

Have you solved this question? If my answer helpful, could you accpet it as solution? thanks for your contribution to improve Power BI.❤️

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.

[v-xiaotang · Accepted solution]

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.

[smpa01]

@pedroccamaraDBI  Provide some sample data

[ValtteriN]

Hi,

You can create a calculation group using tabular editor/visual studio and then place that into a matrix.
Here is the documentation regarding this: https://docs.microsoft.com/en-us/analysis-services/tabular-models/calculation-groups?view=asallprodu...

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[amitchandak]

@pedroccamaraDBI , if they are two different periods by selection, then you have two create 4 measure using independent date table approch 

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

example

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter(all('Date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max))

[pedroccamaraDBI]

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

[amitchandak]

@pedroccamaraDBI , It needs be to independent date table

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

[pedroccamaraDBI]

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?