Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am wondering how to slice revenue year-to-date by year. The revenue year-to-date is for each segment so this looks more like table. I figured out how to make revenue year-to-date by 2017 for each row but I am having difficulty slicing that for each year.
Solved! Go to Solution.
Thanks a ton Scottsen! I got Revenue YTD and Revenue QTD working. Another question: I don't see a DATESPY function for creating Revenue PY. How would I create a measure for this?
Hi @tylerbauer,
You can also use DATEADD function. If you use the following formula to get year-to-day Revenue.
Total Revenue= SUM(Sales[Revenue]) Total Revenue YTD = TOTALYTD(Sales[Total Sales],DateTime[DateKey])
Then you can use the following formula to get last year's revenue.
Total Revenue YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR(DateTime[DateKey])) Total Revenue YTD Last Year = CALCULATE([Total Sales YTD],DATEADD(DateTime[DateKey],-1,year))
If you want to create a calculated column for running totay, please try the following formula similar this thread.
Running Total COLUMN = CALCULATE ( SUM ( Sales[Revenue] ), ALL ( Sales ), Sales[Date] <= EARLIER (Sales[Date]) )
The following articles are helpful, please review.
Cumulative Total
Time Intelligence Functions in DAX
Best Regards,
Angelia
If you have a date table, it would typically look something like:
Revenue YTD := CALCULATE([Total Sales], DATESYTD(Dates[Date]))
Hi Scottsen,
Thanks for your response. I believe this is working. However, when I try to do the same thing to build Revenue QTD, Power BI is telling me I can't do this because of a circular dependency. I want to be able to put 3 columns (Revenue YTD, Revenue PY, and Revenue QTD). How could I do this without getting a ciruclar dependency error?
Thanks,
Tyler
Weird. Can you maybe paste in the measure you tried to use, and ... maybe a screen shot of the tables/relationships?
Oh wait, I have a theory -- are you building these calcs (Revenue PY, etc) as calculated columns in your table? You really want these to be measures.
Hi Scottsen,
I don't mean showing the actual data, just the relationships in the diagram view.
You probably can write calculated columns... but I would not recommend it. If for no other reason than you are going to get in stuck in circular reference hell. See here: https://www.sqlbi.com/articles/understanding-circular-dependencies/
If you write them as measures, they will work against ANY rows you select.
Thanks a ton Scottsen! I got Revenue YTD and Revenue QTD working. Another question: I don't see a DATESPY function for creating Revenue PY. How would I create a measure for this?
Hi @tylerbauer,
You can also use DATEADD function. If you use the following formula to get year-to-day Revenue.
Total Revenue= SUM(Sales[Revenue]) Total Revenue YTD = TOTALYTD(Sales[Total Sales],DateTime[DateKey])
Then you can use the following formula to get last year's revenue.
Total Revenue YTD Last Year = CALCULATE([Total Sales YTD],SAMEPERIODLASTYEAR(DateTime[DateKey])) Total Revenue YTD Last Year = CALCULATE([Total Sales YTD],DATEADD(DateTime[DateKey],-1,year))
If you want to create a calculated column for running totay, please try the following formula similar this thread.
Running Total COLUMN = CALCULATE ( SUM ( Sales[Revenue] ), ALL ( Sales ), Sales[Date] <= EARLIER (Sales[Date]) )
The following articles are helpful, please review.
Cumulative Total
Time Intelligence Functions in DAX
Best Regards,
Angelia
The function you are looking for there is probably SAMEPERIODLASTYEAR()
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |