cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
I_NeedMorePower
Helper III
Helper III

How to deal with balance calcuation with a date slicer?

Hello power people!

I want to calculate balance, which is the amount sum of the first transaction to the last transaction.

But I have a date slicer on the page with both from date and to date.

The issue is if the user moved the from date ahead from the first transaction, the balance sum will be wrong.

 

how to deal with balance in this regard?

is there a "To date" only slicer? or I should fix my measure function?

Because the user needs to know the balance for exmaple of 2019 and the balance of 2020.

 

Thanks in advance!

2 ACCEPTED SOLUTIONS

Hi @I_NeedMorePower ,

 

Thanks for your explanation, it's very clear to me.

 

And I think the below measure is what you want, and I have try it on my desktop, and it works.

 

Measure = CALCULATE(your_measure,FILTER(ALL(your_date),your_date <= MAX(your_date)))

 

How to deal with balance calcuation with a date slicer 4.png

 

Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @I_NeedMorePower ,

 

You change your measure to 

 

 

Measure = CALCULATE(your meaures,FILTER(ALL('SLICER_DATE_TABLE'),''SLICER_DATE_TABLE''[Date]<=MAX(''SLICER_DATE_TABLE''[Date]'[Date])))

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

5 REPLIES 5
AiolosZhao
Memorable Member
Memorable Member

Hi @I_NeedMorePower ,

 

I'm a little confused about your request.

 

Do you want to ignore the slicer or something else?

 

Could you please give samples and the desired result?

 

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi there AiolosZhao!

Sorry if I was not clear!

The situation is, I have transactions that start since 2018 till today (2020) and I have a date slicer on the report page that has a "From Date" & "To Date". I am calculating the balance of the transactions, which is the sum of the first transaction till the last transaction.

My issue is, when moving the "From Date" slicer ahead from the begining (for example moving it to 2019) will result in a wrong balance sum, because it will exclude the previous transactions (transactions of 2018).

And this is my issue.

Is there a way to make the balance measure NOT get affected by the "From Date" slicer? and only get affected with "To Date" slicer?

because if I needed to know the balance of previous year (2019) I will just move the "To Date" to 2019.

 

Thanks for the reply!

Hi @I_NeedMorePower ,

 

Thanks for your explanation, it's very clear to me.

 

And I think the below measure is what you want, and I have try it on my desktop, and it works.

 

Measure = CALCULATE(your_measure,FILTER(ALL(your_date),your_date <= MAX(your_date)))

 

How to deal with balance calcuation with a date slicer 4.png

 

Please try.

Aiolos Zhao





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @AiolosZhao !

 

The measure you provided only works if i'm using the Transactions table date for the slicer.

but what i'm using in the report for the slicer is a date table which is linked to different transaction tables.

The date table starts from 2019, while there are transactions previous to 2019.

How should I modify the measure you provided to make it able to work regarding a date table that is linked to the transaction table with a date key?

I'm sorry if i'm not clear enough. and i'm really glad for the solution you gave me but I tried modifying it to 

Measure = CALCULATE(your_measure,FILTER(ALL(your_date),your_date <= MAX(SLICER_DATE_TABLE[Date])))

 but it didn't work, it still changes when i'm moving the "From date" slicer.

 

Thank you again for your kind help

Hi @I_NeedMorePower ,

 

You change your measure to 

 

 

Measure = CALCULATE(your meaures,FILTER(ALL('SLICER_DATE_TABLE'),''SLICER_DATE_TABLE''[Date]<=MAX(''SLICER_DATE_TABLE''[Date]'[Date])))

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors