DAX calculation based on Start Date and End Date

[Anonymous]

Hello everyone,

Need some help on some DAX calculation as I have very little knowledge about it.

So, I got this kind of data :

Then I added a Date Slicer which has Start and End Date like this :

How is the DAX formula looks like if I want to have :

1. Cost value at the beginning of the period selected (ex: 3/28/2020)

2. Cost value at the end of the period selected (ex: 8/29/2020)

Appreciate any help.

Thanks before.

[mahoneypat]

Yes.  The MIN is getting the min date in current context, which is defined by your slicer.  SELECTEDVALUE can be used only when there is a single value in scope.  Even when a single value is in scope, MIN/MAX/SUM/AVG are still often used instead of SELECTEDVALUE to harvest the value for calculations.

Regards,

Pat

[Anonymous]

Many thanks for your guidance. 

[mahoneypat · Accepted solution]

Please try these two measure expressions in Card visuals.

Starting Sum = var vMinDate = MIN(Table[Date])
return Calculate(SUM(Table[Cost]), FILTER(ALL(Table[Date]), Table[Date]<= vMinDate))

Ending Sum = SUM(Table[Cost])

Regards,

Pat

[Anonymous]

Hi @mahoneypat 

Many thanks. I tried and it is good. Just for my better understanding, when we have this code "var vMinDate = MIN(Table[Date])", doesn't it need to have like SELECTED or SELECTEDVALUE or we can actually just do it with what you've been described ?

I know it is working without it, but since I have slicer for the date, just wondering if just Min(Table[Date]) will refer to the slicer or not. Again it is just for my better understanding. I actually still confuse on this DAX features and how it works.

Thanks again.

[mahoneypat]

You can use LASTNONBLANKVALUE or FIRSTNONBLANKVALUE.  For example,

Min Cost = FIRSTNONBLANKVALUE(Table[Date], MIN(Table[Cost]))

Regards,

Pat

[Anonymous]

Hi @mahoneypat 

Sorry, I didn't get what you;re meaning. But gotta the feeling I probably didn;t explain correctly.

So what my intention is, as that is my inventory data, I want to have my inventory value at the beginning and end of the selected date.

I probably wrong, but I thought we have to do some CALCULATE function for totaling all the item's cost ? So on my mind is something like SUM through my table from the earliest transaction up to date specified in the Start Date, and then SUM through my table again but this time from earliest up to End Date. 

Refer to your suggestion, it looks like we only to get what is the cost of any item that happened on the Start or End Date, is it not ?

Thanks,