Month End Value and Current Date Value

[sekinod]

I have the following data which contains the target value for each month-end.

So, what I want is when selecting a particular date on the slicer, All the months' end value for each month up to  the selected date and the year, and the selected date at the last row. For example, if I select 12/14/2018, the following table should be created. Only the last row value change.

The last row value of the table change from original data because the value should be multiplied by (working days up to that date/total working days in the month) if the date is not equal to the month's end date.

Data Sheet:

TargetData 

[mahoneypat · Accepted solution]

You can't add the slicer date easily, so here is a second measure you can add to the visual to show that.

MaxDate in Target =
VAR seldate =
    MIN ( Dates[Date] )
VAR eomdate =
    CALCULATE (
        MAX ( Target[Date] ),
        ALL ( Dates )
    )
RETURN
    IF (
        seldate < eomdate,
        seldate,
        eomdate
    )

Regards,

Pat

[mahoneypat]

I did use your pbix but it did not have any visuals, so I couldn't see how you were selecting the date.  Here is my file.

Regards,

Pat

[sekinod]

Dear @mahoneypat 

Values getting for measure is correct, but the last column date also shown as monthend date it should be selected date.

Thank you

[Anonymous]

Hi @sekinod,

In fact, current power bi not support to create dynamic calculated columns based on filter/slicer. They are works on different data level and you can't use the child level filter to affect their parent.

Notice: the data level of power bi.
Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view wiht virtual tables (measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[mahoneypat]

I saw same behavior with CALENDAR, so went another way.  Please try this expression instead.  Let me know if it works or if any questions on what it's doing.  This seems to work in a table visual with your Target[Date].

Target to Selected Date =
VAR vDateValue =
    MIN ( Dates[Date] )
VAR vTgtMonthDate =
    CALCULATE (
        MIN ( Target[Date] ),
        ALL ( Dates )
    )
VAR vTgtMonthStart =
    DATE ( YEAR ( vTgtMonthDate ), MONTH ( vTgtMonthDate ), 1 )
VAR vEndDate =
    IF (
        vTgtMonthDate < vDateValue,
        vTgtMonthDate,
        vDateValue
    )
VAR vWkDaysPast =
    CALCULATE (
        SUM ( Dates[Working Day] ),
        FILTER (
            ALL ( Dates[Date] ),
            Dates[Date] >= vTgtMonthStart
                && Dates[Date] <= vEndDate
        )
    )
VAR vWkDays =
    CALCULATE (
        SUM ( Dates[Working Day] ),
        FILTER (
            ALL ( Dates[Date] ),
            Dates[Date] >= vTgtMonthStart
                && Dates[Date] <= vTgtMonthDate
        )
    )
VAR vMonthTgt =
    CALCULATE (
        SUM ( Target[Amount] ),
        ALL ( Dates )
    )
VAR result =
    DIVIDE (
        vWkDaysPast * vMonthTgt,
        vWkDays
    )
RETURN
    result

I used a date slicer on Dates[Date] and used the min value in the expression.  Not sure how you are selected the date.

Regards,

Pat

[sekinod]

Dear @mahoneypat 

It didn't work, visual is empty. If you don't mind could you do it on sample.pbix file I have provided.

Thank you

[mahoneypat]

Since you have a relationship between Dates and DT, please try this instead.  If it doesn't work, please send a link to your pbix (or a mock up of it), and I can get it working on this end.

Tgt = 
VAR vDateValue =
    MIN ( Dates[Date] )
VAR vTgtMonthDate =
    CALCULATE( MIN( DT[Date] ), ALL( Dates ), VALUES( DT[Date]))
VAR vWkDays =
    FILTER (
        CALENDAR (
            DATE ( YEAR ( vTgtMonthDate ), MONTH ( vTgtMonthDate ), 1 ),
            EOMONTH (
                vTgtMonthDate,
                0
            )
        ),
        WEEKDAY ( [Date] )
            IN {
            2,
            3,
            4,
            5,
            6
        }
    )
VAR vWkDaysPast =
    FILTER (
        vWkDays,
        [Date] <= vDateValue
    )
VAR vMonthTgt =
    CALCULATE( MIN ( DT[Amount] ), ALL(Dates), VALUES(DT[Date]))
RETURN
    DIVIDE (
        COUNTROWS ( vWkDaysPast ) * vMonthTgt,
        COUNTROWS ( vWkDays )
    )

Regards,

Pat

[sekinod]

Dear @mahoneypat 

please find sample pbix file here sample.pbix 

Thanks

[mahoneypat]

Here is a measure that should work in a table visual with your Date column from the table you've shown.  I called it targets, so change it to your actual table name.  This assumes you are using your Date table for the slicer but that there is no relationship to the targets table. 

Tgt =
VAR vDateValue =
    MIN ( 'Date'[Date] )
VAR vTgtMonthDate =
    MIN ( Targets[Date] )
VAR vWkDays =
    FILTER (
        CALENDAR (
            DATE ( YEAR ( vTgtMonthDate ), MONTH ( vTgtMonthDate ), 1 ),
            EOMONTH (
                vTgtMonthDate,
                0
            )
        ),
        WEEKDAY ( [Date] )
            IN {
            2,
            3,
            4,
            5,
            6
        }
    )
VAR vWkDaysPast =
    FILTER (
        vWkDays,
        [Date] <= vDateValue
    )
VAR vMonthTgt =
    MIN ( Targets[ Amount ] )
RETURN
    DIVIDE (
        COUNTROWS ( vWkDaysPast ) * vMonthTgt,
        COUNTROWS ( vWkDays )
    )

Regards,

Pat

[sekinod]

Dear @mahoneypat 

This error  I get after creating the measure

This is my data model

This is the modified dax expression I used

Tgt = 
VAR vDateValue =
    MIN ( Dates[Date] )
VAR vTgtMonthDate =
    MIN ( DT[Date] )
VAR vWkDays =
    FILTER (
        CALENDAR (
            DATE ( YEAR ( vTgtMonthDate ), MONTH ( vTgtMonthDate ), 1 ),
            EOMONTH (
                vTgtMonthDate,
                0
            )
        ),
        WEEKDAY ( [Date] )
            IN {
            2,
            3,
            4,
            5,
            6
        }
    )
VAR vWkDaysPast =
    FILTER (
        vWkDays,
        [Date] <= vDateValue
    )
VAR vMonthTgt =
    MIN ( DT[Amount] )
RETURN
    DIVIDE (
        COUNTROWS ( vWkDaysPast ) * vMonthTgt,
        COUNTROWS ( vWkDays )
    )