problem with sum values with dates

[Anonymous]

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.

[v-lid-msft · Accepted solution]

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]

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,

[v-lid-msft · Accepted solution]

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]

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]

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]

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.

[v-lid-msft]

Hi @Anonymous ,

Could you please try to use the following measure in the visual filter if the [Vta_rtl19sd] is a measure?

IsOneMoreDay =
VAR MaxDayOf2019 =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' );
            'Table'[Fecha].[Mes];
            'Table'[Fecha].[Día];
            "2019"; [Vta_rtl19sd]
        );
        [2019] + 0 <> 0
    )
VAR MaxMonth =
    MAXX ( MaxDayof2019; [Mes] )
VAR MaxDay =
    MAXX ( MaxDayof2019; [Día] )
RETURN
    IF (
        SELECTEDVALUE ( Tabla[Fecha].[Mes] ) = MaxMonth
            && SELECTEDVALUE ( 'Tabla'[Fecha].[Día] ) > MaxDay + 1;
        -1;
        1
    )

If it does not work, cloud you please try to share the formula of [Vta_rtl19sd] if it is a measure?

If the [Vta_rtl19sd] is a column of this table, please try to use the following measure:

IsOneMoreDay =
VAR MaxDayOf2019 =
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( 'Table' );
            'Table'[Fecha].[Mes];
            'Table'[Fecha].[Día];
            "2019"; SUM ( 'Table'[Vta_rtl19sd] )
        );
        [2019] + 0 <> 0
    )
VAR MaxMonth =
    MAXX ( MaxDayof2019; [Mes] )
VAR MaxDay =
    MAXX ( MaxDayof2019; [Día] )
RETURN
    IF (
        SELECTEDVALUE ( Tabla[Fecha].[Mes] ) = MaxMonth
            && SELECTEDVALUE ( 'Tabla'[Fecha].[Día] ) > MaxDay + 1;
        -1;
        1
    )

 
Best regards,

[Anonymous]

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 · Accepted solution]

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,

[amitchandak]

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-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601