Help with dax - ytd

[Anonymous]

 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.

[tamerj1]

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!

[Anonymous]

whate is 'date'[MOnth number]

[tamerj1]

The month number 1-12

Do you have multiple years?

[Anonymous]

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

[tamerj1]

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

[Anonymous]

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

[tamerj1]

Hi @Anonymous 

what is your current measure?

[Anonymous]

@amitchandak  

@ValtteriN 

@AlexisOlson 

Please help.

[ValtteriN]

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/

[Anonymous]

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

[Anonymous]

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