need help with a dax measure based aan date

[Krijgersss]

Hello i need some help. I want to make a measure based on a colum "ordered BY" and  "order date" orderd by = text type and order date = date/time type

now i want to make a measure that show me the number of people that have made a order in this year month and last year month so that i know how many people have placed a order in the same month this year/month i want to control this with a year and month slicer 

so if i select 2023 january i want to know how many people have placed a order in january 2023 that has also placed a order in 2022 in january 

[Krijgersss]

this gives me the wrong answer 

it mnust be something like this :
PersonenInBeideJaren =
VAR PersonenDezeMaand =
CALCULATETABLE(
DISTINCT(Orders[OrderBy]),
YEAR(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Year]),
MONTH(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Month])
)
VAR PersonenVorigJaar =
CALCULATETABLE(
DISTINCT(Orders[OrderBy]),
YEAR(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Year]) - 1,
MONTH(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Month])
)
RETURN
COUNTROWS(
INTERSECT(PersonenDezeMaand, PersonenVorigJaar)
)

[Ankur04]

Hi,

good to know that you have figured out the solution. Not sure why your making it complex unnecessarily.

as per the problem scenario which stated all the solution proposed by community members should work.

Thanks,

[mark_endicott]

@Krijgersss - if you're going to use that, rather than the solution I have suggested, then you'll need to add REMOVEFILTERS( Orders[OrderDate] ) as the first filter to your PersonenVorigJaar variable.

VAR PersonenVorigJaar =
CALCULATETABLE(
DISTINCT(Orders[OrderBy]),
REMOVEFILTERS( Orders[OrderDate] ),
YEAR(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Year]) - 1,
MONTH(Orders[OrderDate]) = SELECTEDVALUE(DateTable[Month])
)

[Krijgersss]

this formule returns me blanks so it also doesnt work

PersonenInBeideJaren =

VAR PersonenDezeMaand =

    CALCULATETABLE(

        DISTINCT(RestApiSalesInvoices[ordered_by]),

        YEAR(RestApiSalesInvoices[order_date]) = SELECTEDVALUE(DateDimensions[Jaar]),

        MONTH(RestApiSalesInvoices[order_date]) = SELECTEDVALUE(DateDimensions[MaandNr])

    )

VAR PersonenVorigJaar =

    CALCULATETABLE(

        DISTINCT(RestApiSalesInvoices[ordered_by]),

        REMOVEFILTERS( RestApiSalesInvoices[order_date] ),

        YEAR(RestApiSalesInvoices[order_date]) = SELECTEDVALUE(DateDimensions[Jaar]) - 1,

        MONTH(RestApiSalesInvoices[order_date]) = SELECTEDVALUE(DateDimensions[MaandNr])

    )

RETURN

COUNTROWS(

    INTERSECT(PersonenDezeMaand, PersonenVorigJaar)

)

[mark_endicott]

@Krijgersss - Then I will point you to my earlier suggestion, which you have ignored. 

[Ankur04]

HI,

can you try below measures,

Current_Year_Month_Ordered = Count[Person]

Previous_Year_Month_ordered = Var _Year = Selectedvalue(Calender[Year]) -1

Return

Calculate([Current_Year_Month_Ordered],calender[year] = _Year)

let me know if this works for you. if you still need support then it would helpful if you share pbix file with sample data and expected output.

please feel free mark it as accepted solution, if it works for you.

Thanks,

[mark_endicott]

@Krijgersss - Some more information about your tables may be necessary but the below DAX should help as a starting point:

VAR _current =
    COUNT ( Table[Person] )
VAR _month_now =
    MONTH ( MAX ( Table[Order Date] ) )
VAR _year_previous =
    YEAR ( MAX ( Table[Order Date] ) ) - 1
VAR _previous =
    CALCULATE (
        COUNT ( Table[Person] ),
        REMOVEFILTERS ( Table[Order Date] ),
        MONTH ( Table[Order Date] ) = _month_now
            && YEAR ( Table[Order Date] ) = _year_previous
    )
RETURN
    CALCULATE ( _current, FILTER ( Table, _previous > 0 && _current > 0 ) )

If this works, please accept it as a solution for the visibility of others. 

[Krijgersss]

it comes out one table and i have a calender table for the slicers year and month i also want this measure for last year, this year YTD, last year YTD

i already has the value of the total so if i divide it i get the right % value 

[mark_endicott]

@Krijgersss - Have you tried my solution? If you have a calendar table, you just need to change the section below, as these fields will be used in your slicers: 

VAR _month_now =
    MAX ( Calendar Table[Month] ) 
VAR _year_previous =
    MAX ( Calendar Table[Year] ) ) - 1

This DAX pattern can be used for last year, this year YTD, last year YTD, you'll just need to change the filter condictions in the CALCULATE( ), for YTD you will also need to set a year start date in a variable, like below:

VAR _now = NOW()
VAR _year_start = DATE( YEAR( _now ), MONTH( _now ), 01 )

The conditions for YTD would be something along the lines of:

CALCULATE ( _current, Table[Order Date] >= _year_start )

I believe I have given you everything you need, so please, if this helps accept as the solution. 

[Krijgersss]

so i want a number of people that has made a order in the same month last year and month 

so if i select 2024 januari i want to see a number of people that also made a order in 2023 in january