Forum Discussion
Create YTD as period
Hi,
This is all my fields. I have month with [Quarter] and [Month]. I would like to create another one is [YTD May]. I have error to create YTD May as a new period using DATESYTD ( 'range'month, <21, 05, 01] )
Anyone know what's wrong with my data?
5 Replies
- Greg_Deckler
Community Champion
Anonymous You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008If those don't help, need more information. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - Ashish_Mathur
Super User
Hi,
In the Range Table, we will have to first create a Date column. Then create a Calendar Table with a relationship from the Date column of the Range table to the Date column of the Calendar Table. In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number. Sort the Month name by the Month number. To your visual, drag Year, Month name from the Calendar Table. Write these measures:
Total revenue = sum(Data[Net revenue])
YTD total revenue = calculate([total revenue],datesytd(calendar[date],"31/12"))
Hope this helps.
- AnonymousNot applicable
Hi Ashish Mathur,
Thanks for helping. May I know how to create a date column? I wanted to have YTD as a filter.
It is something in this video but it is limited to one type of value. I want having YTD for revenue, cost and margin as well.
- mahoneypat
Microsoft Employee
You can create a column in your Date table with an expression like this.
IsYTD = var thisyear = YEAR(TODAY())return IF('Date'[Date] >= DATE(thisyear,1,1) && 'Date'[Date] <= TODAY(), "Y", "N")Pat