Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

problem with sum values with dates

Hi , guys i have this problem  I am comparing sales between 2019 and 2018 and I need to add values ​​to one more day in 2018 because I compare day to day. Ex On December 25th was the last day of l...
Need Help
Show and Tell
Tutorial Requests
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can create a measure and use it in visual filter to meet your requirement:

     

    IsOneMoreDay =
IF (
    SELECTEDVALUE ( 'Table'[Date].[MonthNo] )
        = MONTH (
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = 2019 )
            )
        )
        && SELECTEDVALUE ( 'Table'[Date].[Day] )
            > DAY (
                CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = 2019 )
                )
            ) + 1,
    -1,
    1
)

     

     


    If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

     


    Best regards,

     

  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    First of all, we suggest you to delete the shared link if it contain any confidential information or real data.

     

    Please try to use the following measure , and the result should be following if we put the month column into the table visual:

     

    IsOneMoreDay =
VAR MaxDayOf2019 =
    MAXX (
        FILTER (
            SUMMARIZE ( ALLSELECTED ( 'Tabla' ), 'Tabla'[Fecha], "2019", [Vta_rtl19sd] ),
            [2019] + 0 <> 0
        ),
        [Fecha]
    )
RETURN
    IF (
        OR (
            SELECTEDVALUE ( 'Tabla'[Fecha].[NroMes] ) > MONTH ( MaxDayOf2019 ),
            AND (
                SELECTEDVALUE ( 'Tabla'[Fecha].[NroMes] ) = MONTH ( MaxDayOf2019 ),
                SELECTEDVALUE ( 'Tabla'[Fecha].[Día] )
                    > DAY ( MaxDayOf2019 ) + 1
            )
        ),
        -1,
        1
    )

     

     

     


    Best regards,

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can create a measure and use it in visual filter to meet your requirement:

 

IsOneMoreDay =
IF (
    SELECTEDVALUE ( 'Table'[Date].[MonthNo] )
        = MONTH (
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = 2019 )
            )
        )
        && SELECTEDVALUE ( 'Table'[Date].[Day] )
            > DAY (
                CALCULATE (
                    MAX ( 'Table'[Date] ),
                    FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = 2019 )
                )
            ) + 1,
    -1,
    1
)

 

 


If it doesn't meet your requirement, Please show the exact expected result based on the Tables that we have shared.

 


Best regards,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Good day
    Sorry for the holiday delay. I didn't have my equipment at hand. Use the measure you described but it didn't show me the expected result.




    I need that at the maximum date of 2019 (Dec 25) I can make the sale of 2018 with one more day (Dec 26) to be able to compare with the equivalent days and that the other days are not displayed

     

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      We apologise for that we end this topic too early.  Could you please share the formula of Measure "IsOneMoreAfter" which have been modified so we can find the reason why it cannot work?

       


      Best regards,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        IsOneMoreDay =
        IF (
        SELECTEDVALUE ( Tabla[Fecha].[NroMes] )
        = MONTH (
        CALCULATE (
        MAX ( Tabla[Fecha] );
        FILTER ( ALLSELECTED ( 'Tabla' ); YEAR ( 'Tabla'[Fecha] ) = 2019 )
        )
        )
        && SELECTEDVALUE ( 'Tabla'[Fecha].[Día] )
        > DAY (
        CALCULATE (
        MAX ( Tabla[Fecha] );
        FILTER ( ALLSELECTED ( 'Tabla' ); YEAR ( 'Tabla'[Fecha] ) = 2019 )
        )
        ) + 1;
        -1;
        1
        )
                 I tried to change the year "2019" to "2020" but it didn't work either.