cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Charu Member
Member

Calculate based on user selected From date in date slicer

Dear All,

 

DateFilter.PNG

From the above highlighted From date value in date slicer,I need to write measure  
Like,

1) measure=CALCULATE(SUM(table1[qty]),FILTER(table1,table1[Type]="VS"),

FILTER(table1,table1[Date] = table1[SelectedDate]),GROUPBY(table1,table1[Item]))
 
2) SelectedDate measure = selectedvalue(table1[Date])
 
 
 
With out taking MIN(date) in selectedDate measure,I need to get the selected value directly from the date slicer's from part.
 
Thanks in advance
Charu
6 REPLIES 6
RobbeVL Established Member
Established Member

Re: Calculate based on user selected From date in date slicer

Just looking at your Initial question;

 

Try adding the Min date to a Variable.

VAR MIN = Min(TransactiondateDate)

VAR MAX = Max(TransactiondateDate)

RETURN
CALCULATE....

Charu Member
Member

Re: Calculate based on user selected From date in date slicer

Hi @RobbeVL 

 

If I use MIN(transactiondate) then it will be same as the user selected values from the date slicer?

 

Because what I basically need is,I should get the data from the exact value choosen by user that too in the highlighted part from the attached screenshot i.e.; assume(From Date)

RobbeVL Established Member
Established Member

Re: Calculate based on user selected From date in date slicer

Why dont you try and find out ? Smiley Wink

But if you use a visual that is filtered trough that slicer.
Your min and max date will indeed be the one selected in the slicer (make sure you use the same column in the measure as in the slicer)

Charu Member
Member

Re: Calculate based on user selected From date in date slicer

I Tried with the Min transaction date,but I'm getting the expected result only when I select both the from date and to date.

 

 

RobbeVL Established Member
Established Member

Re: Calculate based on user selected From date in date slicer

You'll have to adjust to something like this:
Ideally you would use a DateDimension though

measure=
VAR minDate = MIN(table1[Date])
VAR maxDate = MAX(table1[Date])
RETURN

CALCULATE(
			SUM(table1[qty]),
			FILTER(table1,table1[Type]="VS"),
			FILTER(table1,table1[Date] >= minDate),
			FILTER(table1,table1[Date] <=  maxDate),
			GROUPBY(table1,table1[Item])
			)
Charu Member
Member

Re: Calculate based on user selected From date in date slicer

Hi @RobbeVL 


Looking for your support here.


Facing an issue using the measure you've shared with me.

1) measure =
VAR minDate = MIN(table1[Date])
VAR maxDate = MAX(table1[Date])
RETURN
CALCULATE(
            SUM(table1[qty]),
            FILTER(table1,table1[Type]="VS"),
            FILTER(table1,table1[Date] = minDate), --- equal to min date is the requirement
            GROUPBY(table1,table1[Number])
            )

2) Opening = IF(ISBLANK([AA Opng measure]),0,[AA Opng measure])


That measure works fine when the excel sheet contains exact data,but it's returning the same value for the date which is not there in the sheet.

 

In sheet there are only 3 dates that is 1-Aug-2019,16-Aug-2019 and 5-Aug-2019.If I select Date other than these I should get Zero,but actually for 6th aug 2019 the value of 16th aug 2019 is showing.

Please help me resolve this.

 

Please find the attached sample data for your reference,hope you will get to know what is the exact issue.

https://drive.google.com/drive/folders/1KjFzG-uSs9E8puIck6977Eo_FJ8bbwGc?usp=sharing


Thanks inadvance

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 129 members 1,591 guests
Please welcome our newest community members: