Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Creating a YTD flag on a date

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 ...
Need Help
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
anitaberg
Icon for Helper I rankHelper I
8 years ago

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.