Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI I have two tables, (1) Data (2 ) Calender. Data table looks like below
My Calender table has relation link with abc date, xyz date and def date
i need to get sum of abc value, xyz value, def value in date range. i.e. i need get sum for all in month Oct 2021.
for that i use below measure
Solved! Go to Solution.
Hi @Anonymous
I've changed my measure, you can try this,
1. create an independent calendar table
2. create the measures
_selectedDateMax = CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
_selectedDateMin = CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
abcTotal = CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=[_selectedDateMax]&&Data[abc date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
defTotal = CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=[_selectedDateMax]&&Data[def date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
xyzTotal = CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=[_selectedDateMax]&&Data[xyz date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
result
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.
sample file attached bellow
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.
Hi @Anonymous
you can try this.
abcTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=_selectedDate2&&Data[abc date]>=_selectedDate1))
defTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=_selectedDate2&&Data[def date]>=_selectedDate1))
xyzTotal =
var _selectedDate1=CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
var _selectedDate2=CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
return
CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=_selectedDate2&&Data[xyz date]>=_selectedDate1))
result
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.
HI Mate,
I really appriciate your help. I checked your file but if i add 1 additional column like below
and I use matrix and section values goes to Rows then it shows all static values in matrix, it shows like below
and i need to use month instead of dates in slicer. i need to show those values in matrix only. I hope i could explain better.
Please let me know
Heaps thanks
Hi @Anonymous
the solution I provided was based on the original question you posted. I'll changed my measure based on the new question. but before the adjustment, I need to confirm this,
i need to use month instead of dates in slicer
do you mean you need this type of slicer?
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.
HI Mate, thanks for your quick reply. and sorry i amended my original question.
i need slicer like below
like this
Thanks
Hi @Anonymous
I've changed my measure, you can try this,
1. create an independent calendar table
2. create the measures
_selectedDateMax = CALCULATE(MAX(Calender[Date]),ALLSELECTED(Calender))
_selectedDateMin = CALCULATE(MIN(Calender[Date]),ALLSELECTED(Calender))
abcTotal = CALCULATE(SUM(Data[abc value]),FILTER(ALL(Data),Data[abc date]<=[_selectedDateMax]&&Data[abc date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
defTotal = CALCULATE(SUM(Data[def-value]),FILTER(ALL(Data),Data[def date]<=[_selectedDateMax]&&Data[def date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
xyzTotal = CALCULATE(SUM(Data[xyz-value]),FILTER(ALL(Data),Data[xyz date]<=[_selectedDateMax]&&Data[xyz date]>=[_selectedDateMin]&&Data[Section]=MIN(Data[Section])))
result
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.
sample file attached bellow
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.
Hi Mate, I really appriciate your help and time. all good , I must say you are genious
Heaps thansk
Hey @Anonymous ,
I'm pretty sure that I miss something about the the structure of your data table, but from a first glance I would recommend to reshape the data that the table will look like this
date | type | value
abc-date | abc | value
xyz-date | xyz | value
then you can connect to your date table to the date column and you don't have to struggle with always activating the proper relationship.
Nevertheless, use CROSSFILTER (https://dax.guide/crossfilter/) to deactivate the relationship in addition to USERELATIONSHIP.
Hopefully, this provides what you are looking for.
Regards,
Tom
Hi Tom
I have been wasting whole day to figure out the issue but i could not. can you please help. thanks
HI Tom.
Thanks for your reply. as far as reshape is concern then those 6 columns in my above image is part of 75 columns in excel file. how can I reshape only those 6 coulmns.
and you mentioned me CROSSFILTER another option. would you mind to show / updating my existing meassure.
Very honest i dont want to disturb that entire sheet by rephasing. if this is the only option then let me know
Thanks
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |