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,

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

It didn't work :(. vta_rtlsd19 = SUM (VIEW_GEGCOM_GTN_VTA_SUC_PRD_DIA [vta_rtl19]) / 1000000 vta_rtl18sd is the same but with the 2018 column, I originally have 1 sales column and I separated them to have them per year, I will upload a copy of my pbix so you can see it in more detail

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
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,