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

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. 

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
    Helper 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.