How do I slice Revenue Year to Date Slicing by Year for each row?

[tylerbauer]

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.

[tylerbauer · Accepted 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? 

[Anonymous]

If you have a date table, it would typically look something like:

Revenue YTD := CALCULATE([Total Sales], DATESYTD(Dates[Date]))

[tylerbauer]

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

[Anonymous]

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.

[tylerbauer]

Hi Scottsen, 

Unfortunately, I cannot share the data to that degree of exposure. I am building this calcs as calculated column in my table for the project specification. They want to see the amount change for each row. I believe this can't be accomplished with measures. Please correct me if I'm wrong. 

[Anonymous]

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.

[tylerbauer · Accepted 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? 

[v-huizhn-msft · Accepted solution]

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

[Anonymous]

The function you are looking for there is probably SAMEPERIODLASTYEAR()