How to calculate in measure for date picker selected year

[poko]

Hello everyone,

I'm not sure if I'm my dax variable is correct but I need a year picker (slicer). I need date picker when a user pick a year it will calculate the picked year + 1 and the first day of the following year. Example it means when a user pick year 2021 , dax will return to the date 01/01/2022.

I created this one :

VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])

VAR FirstDayOfNextYear = DATE(SelectedYear+1, 1, 1)

I'm not sure if it's correct because I don't get correct data . 

[Anonymous · Accepted solution]

Hi @poko 

Based on your needs, I have created the following table.

Using the formula you provided, the result will always return 1/1/1901.

You only need to make slight modifications to your Dax to achieve your needs:

M2 = 
var SelectedYear = YEAR(SELECTEDVALUE('Table'[Date]))
RETURN
 DATE(SelectedYear+1, 1, 1)

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @poko 

Has your problem been resolved? If so, could you share your solution so that others with similar issues can benefit from it?

Best Regards,

Jayleny

[vojtechsima]

Hello, @poko 

this measure is essentially fine, depends what you wanna do with it.

I recommend using physical columns for Year (just for clarity) instead of Date.Year.

If you mark your calendar as Date Table, you should lose this option anyway.

then it depends on your exact measure what you wanna achieve.

you can have something like this:

Measure = 
var _year = SELECTEDVALUE('Calendar'[Year])+1
CALCULATE(
SUM('Table'[idk]),
'Calendar'[Year] = _year
)

[poko]

my meausure should pick up values in first top row... with criteria mentioned below..

PendingCasesCount411 =

VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])

VAR FirstDayOfNextYear = DATE(SelectedYear+1, 1, 1)

RETURN

    COUNTROWS(

        FILTER(

            CecCaseDetail,

            VAR TopEvent =

                TOPN(

                    1,

                    FILTER(

                        CecCaseEvent,

                        CecCaseEvent[CecCaseID] = CecCaseDetail[CaseID] &&

                        CecCaseEvent[ToStateOfProceedingID] <> CecCaseEvent[FromStateOfProceedingID] &&

                        CecCaseEvent[EventDate] < FirstDayOfNextYear

                    ),

                    CecCaseEvent[EventOrder],

                    DESC

                )

            VAR SopBeforeDate =

                IF(

                    NOT ISEMPTY(TopEvent),

                    SELECTCOLUMNS(TopEvent, "ToStateOfProceedingID", CecCaseEvent[ToStateOfProceedingID])

                )

            RETURN

                SopBeforeDate IN {2, 3, 4}

        )

    )

[Angith_Nair]

Hi @poko 

Use the below dax:

PendingCasesCount411 =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])
VAR FirstDayOfNextYear = DATE(SelectedYear + 1, 1, 1)

RETURN
    COUNTROWS(
        FILTER(
            CecCaseDetail,
            VAR TopEvent =
                TOPN(
                    1,
                    FILTER(
                        CecCaseEvent,
                        CecCaseEvent[CecCaseID] = CecCaseDetail[CaseID] &&
                        CecCaseEvent[ToStateOfProceedingID] <> CecCaseEvent[FromStateOfProceedingID] &&
                        CecCaseEvent[EventDate] < FirstDayOfNextYear
                    ),
                    CecCaseEvent[EventOrder],
                    DESC
                )
            VAR SopBeforeDate =
                IF(
                    NOT ISEMPTY(TopEvent),
                    SELECTCOLUMNS(TopEvent, "ToStateOfProceedingID", CecCaseEvent[ToStateOfProceedingID])
                )
            RETURN
                SopBeforeDate IN {2, 3, 4}
        )
    )

[vojtechsima]

@poko try adding ALL(CecCaseEvent) to the measure.

   FILTER(
                        ALL(CecCaseEvent),
                        CecCaseEvent[CecCaseID] = CecCaseDetail[CaseID] &&
                        CecCaseEvent[ToStateOfProceedingID] <> CecCaseEvent[FromStateOfProceedingID] &&
                        CecCaseEvent[EventDate] < FirstDayOfNextYear
                    )

btw you can send me sample file (post link from onedrive/google) and I can work on it directly.

[poko]

sure I sent you email

[poko]

I need data that will get all before 01/01/2022, sorry I mentioned it.