topic Re: Help with dax - ytd in DAX Commands and Tips

Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 08:20:45 GMT

I have one query in which i stuck badly.

date	ytd value
1/1/2021	10
2/1/2021	15
17/1/2021	27
3/1/2022	7
5/1/2022	10
16/1/2022	15
17/1/2022	19

so I wanted output like below,

date	ytd value	ytd value-last year-for month
1/1/2021	10	null
2/1/2021	15	null
17/1/2021	27	null
3/1/2022	7	27
5/1/2022	10	27
16/1/2022	15	27
17/1/2022	19	27

Wanted to make ytd-prev year measure, Where my output is last year max of date's value for that month.

It will very helpful if you can ans me.

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 08:30:28 GMT

amitchandak

ValtteriN

AlexisOlson

Please help.

Re: Help with dax - ytd

tamerj1 — Thu, 10 Mar 2022 08:41:15 GMT

Hi Anonymous

what is your current measure?

Re: Help with dax - ytd

ValtteriN — Thu, 10 Mar 2022 08:58:03 GMT

Hi,

Try something like this:

Measure 19 =

var _m = MONTH(max('Table (7)'[date]))

var _y = year(max('Table (7)'[date]))-1

return

CALCULATE(MAX('Table (7)'[ytd value]),ALL('Table (7)'[date]), year('Table (7)'[date])=_y,

month('Table (7)'[date])=_m)

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 09:26:53 GMT

Sorry my mistake in explanation may be.

Its giving me random value in my actual table.

Date	YTD(Its measure)
1/1/2021	10
2/1/2021	4
13/1/2021	28
22/1/2021	7
1/2/2021	10
6/2/2021	12
13/2/2021	55
27/2/2021	22
1/2/2022	17
7/1/2022	5
11/1/2022	14
1/2/2022	3
3/2/2022	12
13/2/2022	13

Date	YTD(Its measure)	YTD(max dates number)(previous year)(same month)
1/1/2021	10	null
2/1/2021	4	null
13/1/2021	28	null
22/1/2021	7	null
1/2/2021	10	null
6/2/2021	12	null
13/2/2021	55	null
27/2/2021	22	null
1/2/2022	17	7
7/1/2022	5	7
11/1/2022	14	7
1/2/2022	3	22
3/2/2022	12	22
13/2/2022	13	22

Output should be previous year-same month-for that max date's ytd number

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 09:31:16 GMT

Sorry my mistake in explanation may be.

Its giving me random value in my actual table.

Date	YTD(Its measure)
1/1/2021	10
2/1/2021	4
13/1/2021	28
22/1/2021	7 (This is result for any date of jan/2022)
1/2/2021	10
6/2/2021	12
13/2/2021	55
27/2/2021	22 (This is result for any date of feb/2022)
1/2/2022	17
7/1/2022	5
11/1/2022	14
1/2/2022	3
3/2/2022	12
13/2/2022	13

Date	YTD(Its measure)	YTD(max dates number)(previous year)(same month)
1/1/2021	10	null
2/1/2021	4	null
13/1/2021	28	null
22/1/2021	7	null
1/2/2021	10	null
6/2/2021	12	null
13/2/2021	55	null
27/2/2021	22	null
1/2/2022	17	7
7/1/2022	5	7
11/1/2022	14	7
1/2/2022	3	22
3/2/2022	12	22
13/2/2022	13	22

Output should be previous year-same month-for that max date's ytd number

Re: Help with dax - ytd

tamerj1 — Thu, 10 Mar 2022 10:05:57 GMT

Hi Anonymous
You may try
For Previous Year To Date

PYTD = VAR LastMonthInYearAvailable = MAX ( 'Date'[Month Number] ) VAR LastYearAvailable = SELECTEDVALUE ( 'Date'[Year Number] ) VAR PreviousYearAvailable = LastYearAvailable - 1 VAR Result = CALCULATE ( [Sales Amount], REMOVEFILTERS ( 'Date' ), 'Date'[Month Number] <= LastMonthInYearAvailable, 'Date'[Year Number] = PreviousYearAvailable ) RETURN Result

For Max Previous Year To Date

PYTD Month Max. = MAXX ( VALUES ( 'Date'[Month Number] ), IF ( NOT ISBLANK ( [PYTD] ), CALCULATE ( [Sales PYTD], ALLEXCEPT ( 'Date', 'Date'[Month Number], 'Date'[Month], 'Date'[Year Month Number], 'Date'[Year Month] ) ) ) )

You may need to adjst the names of the columns in the date table or add/remove some columns to/from the ALLEXCEPT depending on you date table structure.

Thank you and have a great day!

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 10:19:08 GMT

whate is 'date'[MOnth number]

Re: Help with dax - ytd

tamerj1 — Thu, 10 Mar 2022 10:21:27 GMT

The month number 1-12

Do you have multiple years?

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 10:30:43 GMT

Yes. Your solution is giviing me very different thing.

Sorry my mistake in explanation may be.

Its giving me random value in my actual table.

Date	YTD(Its measure)
1/1/2021	10
2/1/2021	4
13/1/2021	28
22/1/2021	7 (This is result for any date of jan/2022)
1/2/2021	10
6/2/2021	12
13/2/2021	55
27/2/2021	22 (This is result for any date of feb/2022)
1/2/2022	17
7/1/2022	5
11/1/2022	14
1/2/2022	3
3/2/2022	12
13/2/2022	13

Date	YTD(Its measure)	YTD(max dates number)(previous year)(same month)
1/1/2021	10	null
2/1/2021	4	null
13/1/2021	28	null
22/1/2021	7	null
1/2/2021	10	null
6/2/2021	12	null
13/2/2021	55	null
27/2/2021	22	null
1/2/2022	17	7
7/1/2022	5	7
11/1/2022	14	7
1/2/2022	3	22
3/2/2022	12	22
13/2/2022	13	22

Output should be previous year-same month-for that max date's ytd number

Re: Help with dax - ytd

tamerj1 — Thu, 10 Mar 2022 11:13:21 GMT

Anonymous
Can you please send me your original measure code? It really matters.

Re: Help with dax - ytd

Anonymous — Thu, 10 Mar 2022 15:24:06 GMT

this is my issue, I wanted to find max date of same month from last year.

below is my measure,

PYDT =

var _date = date(year(DATEADD(EAS[reportdate],-1,YEAR)),MONTH(DATEADD(EAS[reportdate],-1,YEAR)),10)

var _getdate = calculate(MIN(EAS[reportdate]),ENDOFMONTH(_date))

var result = CALCULATE([NR (YTD) in lakhs],EAS[reportdate] = _getdate && EAS[strategy] <> "XXXX")

return result