How to fix Month # and Day # for the YTD year-over-year comparison?

[powerbiasker]

I want to use Month # and Day # based on the 2019 max date in a Fact table for the YTD year-over-year comparison. For example, if the 2019 max date in the Fact table is 2019-11-01; I want to do the comparison like:

• (20150101 - 20151101) vs (20160101 - 20161101) vs (20170101 - 20171101) vs (20180101 - 20181101) vs (20190101 - 20191101)

How should I do? DATESYTD()? TotalYTD()?

I use the following code, but it only gives me the YTD comparison about (YYYY-01-01 to the max date for each year).

cntd Value Each Year YTD_cross = 
    CALCULATE (
        [cntd], 
        DATESBETWEEN (
            Date_dim[Date],
            MIN ( Date_dim[Date] ),
date(year(MAX ( Date_dim[Date] )), 
     month(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )),
     day(CALCULATE ( MAX ( 'factTB'[project_due_dt] ), ALL ( 'factTB'[project_due_dt], 'factTB'[organization_type], 'factTB'[project_status]) )))
                        )
                )

[powerbiasker]

Thank u.

[MFelix · Accepted solution]

Hi @powerbiasker ,

Is you value based only on the maximum date of the table or do you want to have it dinamically choosen?

If it's only based on the maximum date you can create an additional column on your dimdate to get an 1 value something similar to:

Dateselect =
IF (
    Dimdate[Date]
        <= DATE ( YEAR ( Dimdate[Date] ); MONTH ( MAX ( 'Table'[Due Date] ) ); DAY ( MAX ( 'Table'[Due Date] ) ) );
    1;
    0
)

Then just make you measure similar to this:

MEASURE = CALCULATE(SUM('Table'[Column1]); Dimdate[Dateselect] = 1)

Just adjust your the name of table and columns to your model.

Regards,

MFelix

[amitchandak]

Try something like this

Year  = CALCULATE(SUM(table[column]),DATESYTD('Date'[Date Filer]))
Last Year  = CALCULATE(SUM(table[column]),DATESYTD(dateadd('Date'[Date Filer],-12,MONTH)))


Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),('Date'[Date Filer])))
Last Year Sales = CALCULATE(TOTALYTD(sum(Sales[Sales Amount]),dateadd('Date'[Date Filer].-12,month)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[VasTg]

@powerbiasker   

Try Parallelperiod...Let us know if it helps..

PARALLELPERIOD('Calendar'[DATES],-12,MONTH))
PARALLELPERIOD('Calendar'[DATES],-24,MONTH))
PARALLELPERIOD('Calendar'[DATES],-36,MONTH))