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 loading the report but for 2018 I need to load it one more day and not show the other values ​​that follow it just one more day.



 

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,

     

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    In 2019 measure filter for max date +1

     

    Final 2019 =
Var _Max = max(date[date])+1
return
calculate(sum([2019]),filter(date[date]<=_Max)

    Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
    Thanks. My Recent Blog -
    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
    https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
    https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

  • 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,

         

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

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,