topic Re: need help with a dax measure based aan date in DAX Commands and Tips

need help with a dax measure based aan date

Krijgersss — Thu, 22 Aug 2024 09:01:11 GMT

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

Re: need help with a dax measure based aan date

mark_endicott — Thu, 22 Aug 2024 09:27:36 GMT

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.

Re: need help with a dax measure based aan date

Krijgersss — Thu, 22 Aug 2024 10:12:34 GMT

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

Re: need help with a dax measure based aan date

Krijgersss — Thu, 22 Aug 2024 10:14:23 GMT

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

Re: need help with a dax measure based aan date

mark_endicott — Thu, 22 Aug 2024 10:24:02 GMT

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.

Re: need help with a dax measure based aan date

Ankur04 — Thu, 22 Aug 2024 11:03:40 GMT

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,

Re: need help with a dax measure based aan date

Krijgersss — Thu, 22 Aug 2024 12:19:04 GMT

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)
)

Re: need help with a dax measure based aan date

mark_endicott — Thu, 22 Aug 2024 12:24:49 GMT

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]) )

Re: need help with a dax measure based aan date

Ankur04 — Thu, 22 Aug 2024 12:35:33 GMT

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,

Re: need help with a dax measure based aan date

Krijgersss — Thu, 22 Aug 2024 12:44:55 GMT

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)

)

Re: need help with a dax measure based aan date

mark_endicott — Thu, 22 Aug 2024 15:03:54 GMT

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