YTD functions for Items with no sales YTD

[dniedrauer]

The time intelligence in Power BI is nice, but I'm having issues with it that, either way, requires a manual input in specify the year meant by "year to date." 

Here's the scenario: 

100 products in the data set

80 were sold in both 2017 and 2016

15 were sold in 2016 but not 2017

5 were sold in 2017 but not 2016

calculate(sum(sales usd),datesytd('table' [period]) has ytd sales for all 100 products...which is misleading. But, of course, in aggregate, the total sales ytd would be correct under this formula. 

I have a workaround that involves current year ---> calculate([sumofsales],'calendar'[year]="2017") and last year to date = (calculate([sumofsales],'calendar'[year]="2016")/12)*calculate(distinctcount('main'[period]),datesytd('main'[period]) but then why even bother with the time intelligence functions? 

[Eric_Zhang]

---

@dniedrauer wrote:

The time intelligence in Power BI is nice, but I'm having issues with it that, either way, requires a manual input in specify the year meant by "year to date." 

Here's the scenario: 

100 products in the data set

80 were sold in both 2017 and 2016

15 were sold in 2016 but not 2017

5 were sold in 2017 but not 2016

calculate(sum(sales usd),datesytd('table' [period]) has ytd sales for all 100 products...which is misleading. But, of course, in aggregate, the total sales ytd would be correct under this formula. 

I have a workaround that involves current year ---> calculate([sumofsales],'calendar'[year]="2017") and last year to date = (calculate([sumofsales],'calendar'[year]="2016")/12)*calculate(distinctcount('main'[period]),datesytd('main'[period]) but then why even bother with the time intelligence functions? 

---

@dniedrauer

Would you mind post any sample data(in plain text) and expected output?

[dkay84_PowerBI]

Is your Table[period] a date table?

[dniedrauer]

No, not in this case. It doesn't seem to make a difference though. 

[dkay84_PowerBI]

The time intelligence functions will not work properly without a full date table, with a row for every date in the range of your data.  Try creating a date table and linking to your fact table and see if you can get the functions to work.