topic Re: Get Value for month end and current date in DAX Commands and Tips

Get Value for month end and current date

sekinod — Fri, 11 Dec 2020 13:28:17 GMT

The following data contains the target values which are given for each month end.

What I want is when select a date in slicer, all the month-end values must be shown within the selected date year up to selected date month and current date. For example if I select 12/14/2018 result table should be the following. For selected date month value should be multiplied by (working day up to that date in the month/total working dates in the month) because selected date is less than month end, other month-end value same as the data file month end value.

I have attached sample pbix file:

sample.pbix

Thank you

Re: Get Value for month end and current date

AlB — Fri, 11 Dec 2020 15:01:00 GMT

sekinod

Where exactly do you get the target value for 01/31/2018 from? I see many values for that date on the Target[Amount] and none matches that value. How do you select the value from all the values for that date?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Get Value for month end and current date

sekinod — Fri, 11 Dec 2020 15:08:37 GMT

Dear AlB

It was according to product wise. sum of productof All "FD+1+2+3 ". And it should be filtered by branch.

Thanks

Re: Get Value for month end and current date

AlB — Fri, 11 Dec 2020 15:23:16 GMT

It was according to product wise. sum of productof All "FD+1+2+3 ". And it should be filtered by branch.

Thanks

sekinod

?? Please provide an example with the numbers to show how you come up with that figure. I have no idea what

product wise. sum of productof All "FD+1+2+3 "

means

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Get Value for month end and current date

sekinod — Fri, 11 Dec 2020 15:56:47 GMT

Dear AlB

Here is example.

Value is filtered to Date of 1/31/18 and Branch 106 and for all three products. sum of all three amount should be shown as on the previous table.

Re: Get Value for month end and current date

AlB — Fri, 11 Dec 2020 17:21:20 GMT

Dearsekinod

Here is the measure. I also added an auxiliary dates tables to use for the slicer. See it all at work in the attached file. I would recommend to add the working days column in Dates in PQ, or in DAX but avoiding the bidirectional relationship with Holidays

Target measure = VAR selectedYear_ = SELECTEDVALUE ( AuxDates[Year] ) VAR selectedYM_ = SELECTEDVALUE ( AuxDates[Year MonthNr] ) VAR selectedDate_ = SELECTEDVALUE ( AuxDates[Date] ) VAR currentYear_ = SELECTEDVALUE ( Dates[Year] ) VAR currentYM_ = SELECTEDVALUE ( Dates[Year MonthNr] ) VAR currentDate_ = SELECTEDVALUE ( Dates[Date] ) RETURN IF ( currentYear_ = selectedYear_, SWITCH ( TRUE (), currentYM_ < selectedYM_, IF ( EOMONTH ( currentDate_, 0 ) = currentDate_, CALCULATE ( SUM ( Target[Amount] ), Target[Product] IN { "FD", "1", "2", "3" } ) ), currentYM_ = selectedYM_, IF ( currentDate_ = selectedDate_, VAR totalTargetMonth_ = CALCULATE ( SUM ( Target[Amount] ), Dates[Date] = EOMONTH ( currentDate_, 0 ), Target[Product] IN { "FD", "1", "2", "3" } ) VAR totalWDaysInMonth_ = CALCULATE ( SUM ( Dates[Working Day] ), Dates[Year MonthNr] = currentYM_, ALL ( Dates ) ) VAR totalWDaysInPeriod_ = CALCULATE ( SUM ( Dates[Working Day] ), Dates[Year MonthNr] = currentYM_, Dates[Date] <= currentDate_, ALL ( Dates ) ) VAR ratio_ = DIVIDE ( totalWDaysInPeriod_, totalWDaysInMonth_ ) RETURN totalTargetMonth_ * ratio_ ) ) )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: Get Value for month end and current date

sekinod — Sat, 12 Dec 2020 02:40:20 GMT

Dear AlB,

Thank you for your help, it is much appreciated. I accepted your answer as the solution. If is not much could please tell me how I can get the total at bottom of the table visualization.

Re: Get Value for month end and current date

AlB — Sun, 13 Dec 2020 12:24:07 GMT

sekinod

Create a new measure using the one we already had:

Target measure TOT = SUMX( DISTINCT(Dates[Date]), [Target measure])

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers