Repeat values based on other value

[idontexist]

Hi all,

I want to repeat values as you can see in column "should be plan". It should be based on column "Fact". If in this column there are any values so plan should be repeated. "Plan" i have 1 value for whole month, so you can try to use logic with "first-day-of-month" = 1.

This i use, but it did not work: Matrix Plan = if([Fact]<>"";CALCULATE(sum('table1'[Plan]);'Date'[Day Of Month]="1";""))

DSR Code	Date	Fact	Plan	Should be plan
RRUM116	01.08.2017	173	194	194
RRUM116	07.08.2017	177		194
RRUM116	08.08.2017
RRUM116	14.08.2017	3		194
RRUM116	15.08.2017
RRUM116	21.08.2017	3		194
RRUM116	28.08.2017	3		194

Next, i have tried this: Matrix Plan = SWITCH(TRUE();isblank([Fact]);blank();CALCULATE('table1'[Plan];'Date'[Day Of Month]=1))

But it returned me: 

DSR Code	Date	Fact	Plan	Should be plan
RRUM116	01.08.2017	173	194	194
RRUM116	07.08.2017	177		0
RRUM116	08.08.2017
RRUM116	14.08.2017	3		0
RRUM116	15.08.2017
RRUM116	21.08.2017	3		0
RRUM116	28.08.2017	3		0

Almost as i want 😄 

[v-chuncz-msft]

@idontexist,

You may refer to the following DAX.

Column =
VAR y =
    YEAR ( Table1[Date] )
VAR m =
    MONTH ( Table1[Date] )
RETURN
    IF (
        NOT ( ISBLANK ( Table1[Fact] ) ),
        LOOKUPVALUE (
            Table1[Plan],
            Table1[DSR Code], Table1[DSR Code],
            Table1[Date], DATE ( y, m, 1 )
        )
    )

[idontexist]

@v-chuncz-msft

formulae YEAR and MONTH want to get date in format. Not from column.

So this isn't working for me 😞

[Zubair_Muhammad]

Hi @idontexist

See the attached file here

(With your sample data)

It works when I use your sample

[idontexist]

@Zubair_Muhammad

Sorry, did not mentioned that before... But all values exist in different tables.

Below screenshot 

[Zubair_Muhammad]

Hi @idontexist

Actually I gave formula for a calculated column.

EARLIER doesn't work in a MEASURE.

You can share your file via Onedrive or googledrive.

I will try to help

[idontexist]

@Zubair_Muhammad

Unfortunately i can't do that, because it's a "live" data, not local. Without access you will not be able to see anything, and i haven't such persmissions to give your proper rights. 

Maybe we can use some workaround... Create table with "Plan" that will be repeated for every day of month. So i can use something like: Matrix plan = IF ( NOT ( ISBLANK ([Fact]) ); [Plan])

Anyway thanks for help!

[Zubair_Muhammad]

Hi @idontexist

Try this MEASURE.

It works with your sample data

Measure =
VAR FirstValue =
    CALCULATE (
        VALUES ( TableName[Plan] ),
        FILTER (
            ALL ( Tablename ),
            MONTH ( TableName[Date] ) = MONTH ( VALUES ( TableName[Date] ) )
                && TableName[Date]
                    = MINX (
                        FILTER (
                            ALL ( TableName ),
                            MONTH ( TableName[Date] ) = MONTH ( VALUES ( TableName[Date] ) )
                        ),
                        TableName[Date]
                    )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( SELECTEDVALUE ( TableName[Fact] ) ) ), FirstValue )

[idontexist]

Hi @Zubair_Muhammad

It also didn't help. But i have figured out how i can do that. It's almost looks like my first formula:

Matrix plan = IF ( NOT ( ISBLANK ( [Compliant Distribution] ) ); CALCULATE(sum('Local Measure'[Measure Quantity]);'Date'[Day Of Month]=1))

It just didn't show me correct values if i use "date" in fields:

But if i add "Day of Month" which i use in formula, instead of "Date" it will calculate as i want. Only without total sum, unfortunately.

[idontexist]

Hi @Zubair_Muhammad

It return me an error. Words in Russian could be translated as "Error during calculating measure...".

 

[Zubair_Muhammad]

Hi @idontexist

Please try replacing

VALUES (Local Measure[Measure Quantity])

with

SUM (Local Measure[Measure Quantity])

[Zubair_Muhammad]

Hi @idontexist

Try this calculated Column

Should_be_plan =
VAR FirstValue =
    CALCULATE (
        VALUES ( TableName[Plan] ),
        FILTER (
            ALL ( Tablename ),
            MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
                && TableName[Date]
                    = MINX (
                        FILTER (
                            ALL ( TableName ),
                            MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
                        ),
                        TableName[Date]
                    )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( TableName[Fact] ) ), FirstValue )

[idontexist]

Hi @Zubair_Muhammad

I've tried and it did not work. Red value below, it marks me as wrong. So formula do nothing.

Should_be_plan =
VAR FirstValue =
    CALCULATE (
        VALUES ( TableName[Plan] ),
        FILTER (
            ALL ( Tablename ),
            MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
                && TableName[Date]
                    = MINX (
                        FILTER (
                            ALL ( TableName ),
                            MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
                        ),
                        TableName[Date]
                    )
        )
    )
RETURN
    IF ( NOT ( ISBLANK ( TableName[Fact] ) ), FirstValue )