need urgent help with a DAX measure

[redface94]

hi, I would need your assistance because I have to create a measure, to calculate selective based on YTD (e.g. Selective April = YTD April - YTD May. So when I select the month slicer, the selective sum has to be calculated automatically. I already have all the YTD months in columns but also a single YTD column (attached). 

[devanshi]

YTD = TOTALYTD(SUM(Profit), [Date])
selected = 
VAR selectedmonth = SELECTEDVALUE(MONTH([Date]))
RETURN
CALCULATE([YTD],  ALL(datetable),  [Date] = selectedmonth  &&  [Date] > selectedmonth)

[redface94]

hi, I tried to adapt the proposed formula, however, unfortunatelly it return 0 as result:

Measure =

VAR currDate =

    MAX ( 'CCA Database'[Reporting Date].[Date] )

RETURN

    IF (

        MONTH ( currDate ) < 12,

        CALCULATE (

            SUM ( 'CCA Database'[Actual YTD]),

            FILTER (

                ALLSELECTED ( 'CCA Database' ),

                YEAR ( 'CCA Database'[Reporting Date].[Date] ) = YEAR ( currDate )

                    && 'CCA Database'[Reporting Date].[Date] <= currDate

            )

        )

            - CALCULATE (

                SUM ( 'CCA Database'[Actual YTD] ),

                FILTER (

                    ALLSELECTED ( 'CCA Database' ),

                    [Reporting Date].[Date] >= DATE ( YEAR ( currDate ), 1, 1 )

                        && [Reporting Date].[Date]

                            <= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, DAY ( currDate ) )

                )

            )

    )

[Anonymous]

Hi @redface94 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[redface94]

hi Xiaoxin, I've tried to attach the excel filr but the system does not accept it, so I paste here some dummy values, in exact the same format which I use:

Value Type	Reporting Period	Reporting Date	Dummy one	Dummy 2	Calendar Year	Responsible	Dummy 3	Du,mmy 4	Dymmy 5	Dummy 6	Dyummy 7	Country	Country Name	Region	Dummy 8	Duummy 9	Dymmy 10	Dymmy 11	Dymmy 12	Dymmy 13	Dymmy 14	Dymmy 15	Dymmy 16	Dymmy 17	Actual	Actual YTD	dummy18	dummy19	dummy20	dummy21	dummy22	dummy23
Cost	01.2023	31/01/2023	7574504		2023							JP	Japan	Asia/Pacific											9,186	208
Cost	01.2023	31/01/2023	2545975		2023							JP	Japan	Asia/Pacific											3,668	6,986
Cost	01.2023	31/01/2023	1667412		2023							JP	Japan	Asia/Pacific											6,414	6,699
Cost	01.2023	31/01/2023	7798873		2023							JP	Japan	Asia/Pacific											9,148	4,022
Cost	01.2023	31/01/2023	4655728		2023							JP	Japan	Asia/Pacific											21	7,984
Cost	02.2023	28/02/2023	6059819		2023							DE	Germany	Europe											5,228	8,726
Cost	02.2023	28/02/2023	800714		2023							DE	Germany	Europe											2,754	10,836
Cost	02.2023	28/02/2023	8405864		2023							DE	Germany	Europe											8,689	171
Cost	02.2023	28/02/2023	5597513		2023							DE	Germany	Europe											8,630	627
Cost	02.2023	28/02/2023	3578614		2023							DE	Germany	Europe											6,167	4,334
Cost	02.2023	28/02/2023	808156		2023							DE	Germany	Europe											153	1,495
Cost	02.2023	28/02/2023	5936417		2023							DE	Germany	Europe											8,390	245,415
Cost	02.2023	28/02/2023	7228652		2023							DE	Germany	Europe											7,703	2,165
Cost	02.2023	28/02/2023	6209322		2023							DE	Germany	Europe											6,637	57

[Anonymous]

Hi @redface94,

I test on the dummy data and find the formula works, anything special exist on your report or Dax expressions?

diff = 
VAR currDate =
    MAX ( 'Table'[Reporting Date])
RETURN
    IF (
        MONTH ( currDate ) < 12,
        CALCULATE (
            SUM ( 'Table'[Actual YTD]),
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( [Reporting Date] ) = YEAR ( currDate )
                    && [Reporting Date] <= currDate
            )
        )
            - CALCULATE (
                SUM ( 'Table'[Actual YTD] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    [Reporting Date] >= DATE ( YEAR ( currDate ), 1, 1 )
                        && [Reporting Date]
                            <= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, DAY ( currDate ) )
                )
            )
    )

Regards,

Xiaoxin Sheng

[redface94]

hello, Is there a possibility to insert here my pbix? becasue the measure still shows 0 unfortunatelly and I can't figure out the reason for it. thank you! 

[redface94]

Dear Xiaoxin, 

I checked again and I think I have an issue with the date format. The measure is working but when I add the date in the table, there is an error (will attach it). Also, the measure displays 0 when I remove Actual and Actual YTD columns and I will need only the difference to be displayed in a separate table. I will attach also a screenshot of my date format. thank you so much!

[Anonymous]

HI @redface94,

You can try to use the following measure formula to get the difference between current and following month YTD:

formula =
VAR currDate =
    MAX ( Calendar[Date] )
RETURN
    IF (
        MONTH ( currDate ) < 12,
        CALCULATE (
            SUM ( Table[Value] ),
            FILTER (
                ALLSELECTED ( Table ),
                YEAR ( [Date] ) = YEAR ( currDate )
                    && [Date] <= currDate
            )
        )
            - CALCULATE (
                SUM ( Table[Value] ),
                FILTER (
                    ALLSELECTED ( Table ),
                    [Date] >= DATE ( YEAR ( currDate ), 1, 1 )
                        && [Date]
                            <= DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, DAY ( currDate ) )
                )
            )
    )

Regards,
Xiaoxin Sheng