Get Value for month end and current date

[sekinod]

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

[AlB · Accepted solution]

Dear@sekinod

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 

[AlB · Accepted solution]

@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 

[AlB · Accepted solution]

Dear@sekinod

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 

[sekinod]

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. 

[AlB]

@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 

[sekinod]

Dear @AlB 

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

Thanks

[AlB]

---

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 

[sekinod]

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.