Creating a YTD flag on a date

[imaddrell]

Hi

Could someone help?

I have read a lot of questions and answers that focus on summing or counting a field based on a year to date calculation, but I can't find how to simply flag a date to show the date as being in the current year to date? I have a table called DateKey and a column called Date, the column shows every date (by individual days from 01/01/2012 to 30/06/2050) with a row for each day. I tried this formula: YTD = DATESYTD(DateKey[Date]), this I thought would have flagged a date as being in the latest year to date. But when I create a colum with this formula I receive the following error: A table of multiple values was supplied where a single value was expected. I read into this error message and it seems to have an issue with duplicates, but no duplicates are in the colum called Date.

Ideally does anyone have a formula to flag a day as being in the last year to date. I suppose I could do with a Financial Year that starts on the 1st of July each year.

Any help would be much appreciated.

Thanks

Ian

[anitaberg]

Hi! Did you find a solution to this? To have a YTD flag as dimension/column in a table containing a lot of dates? I have used this in QlikView, and I see that it would be useful in Power BI too. I also see that most of what I read is related to creating measures using specific sum functions, but that is not what I want to do. 

I can see a problem in PowerBI where the date table is created at one point, while in Qlik View an application is loaded every day. Also the date table is generated each day, so that each day the ytd flag will be added to new dates. 

[imaddrell]

Hi Anita

No, the solution proposed simply added a measure of YTD. What I really wanted was to flag a date on a date table as being in the last year to date. What I did was create sum of the measures I needed and included them in the table I was authoring on a report. Not ideal, but it worked.

Thanks

Ian

[anitaberg]

I just tested to create this YTD flag now in my date table. The date table contains Date, Year, Month Number, Month, Quarter etc.

Then I created one column called YTD comparision end date with the formula YTD comparision end date = TODAY(). Then another column YTD comparison start date = STARTOFYEAR (DateKey[YTD comparision end date].[Date]). With these two columns I made the YTD flag: YTD flag = IF(DateKey[Date] <=DateKey[YTD comparision end date]&&DateKey[Date]>=DateKey[YTD comparison start date],1, 0)  Looks like I get the correct flag on the correct dates.

Only question is how dynamic this is. Tomorrow I need one more date to have the YTD flag. If it does not get that then I would rather create the date table on SQL Server rather than inside Power BI.

[v-ljerr-msft]

Hi @imaddrell,

---

Ideally does anyone have a formula to flag a day as being in the last year to date. I suppose I could do with a Financial Year that starts on the 1st of July each year.

---

The DATESYTD function has an optional parameter called year_end_date, which is a literal string with a date that defines the year-end date. The default is December 31. 

So in your scenario, you can specify it as "July 1" in your YTD measure. The formula below is for your reference. 

YTD Sales=
CALCULATE (
    SUM ( InternetSales_USD[SalesAmount_USD] ),
    DATESYTD ( DateTime[DateKey], "July 1" )
)

Regards

[imaddrell]

Hi

Thanks for the response.

I am not sure about the Summarize function? Do you mean to create a column with an amount of sales in the current year to date? I have done this but I am unsure what the result is telling me? It seems to be multiplying all of the sales data exponentially and not really showing a year to date figure. Do you know how the field would be queried or displayed? Also, why can't a flag be added to the date table to show the date as being in the last year to date as opposed to creating the sum?

Thanks