Forum Discussion

idontexist's avatar
idontexist
Helper I
8 years ago

Repeat values based on other value

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 CodeDateFactPlanShould be plan
RRUM11601.08.2017173194194
RRUM11607.08.2017177 194
RRUM11608.08.2017   
RRUM11614.08.20173 194
RRUM11615.08.2017   
RRUM11621.08.20173 194
RRUM11628.08.20173 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 CodeDateFactPlanShould be plan
RRUM11601.08.2017173194194
RRUM11607.08.2017177 0
RRUM11608.08.2017   
RRUM11614.08.20173 0
RRUM11615.08.2017   
RRUM11621.08.20173 0
RRUM11628.08.20173 0

 

Almost as i want :D 

12 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
      idontexist
      Helper I
      8 years ago

      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 )

       

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    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 )
        )
    )