Forum Discussion
Post PartisanTotal 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 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
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
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.
8 Replies
- amitchandak
Super User
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))
pedroccamaraDBIHello 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 doneNet 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])RETURNCALCULATE( [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
- ValtteriN
Community Champion
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=asallproducts-allversions
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! 
smpa01pedroccamaraDBI Provide some sample data
v-xiaotangCommunity Support
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
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
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.
