Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
76 | |
63 | |
55 |
User | Count |
---|---|
142 | |
107 | |
89 | |
84 | |
65 |