Percentage of time with a value.

[Anonymous]

Hey There! 

Im working at a CO2 reduction dax calculation, and I have some issues with it.

I made a filter of year, but in year 2020 I only have data from January to April.

In my calculation i Divide last years CO2 usage with this year. Which means that even though that I try to use "Sameperiodlastyear" I cant get the result that iam searching for.

Here you have the table with testdata for this year(CO2 udledning i alt), and last year(Sidste år CO2 udledning).

My calculation in "reduction":

Reduktion i alt tons = CALCULATE(FORMAT(DIVIDE([CO2 udledning i alt];SUM(Query1[Reduktion i alt]));"percent"))

Is it possible to make a calculation, which checks the months which has a value in "selected year", and then only counts those in the year before?

[amitchandak]

@Anonymous , In case you have date and using date calendar and time intelligence these should work

// When you have slicer or calendar that end at april 2020
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))

// Force to stop at today

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

////// Force to stop max date of the fact

YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

explained in my webinar : https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

[negi007]

You can do the YTD calculation provided you have date field in your data. You need to follow these steps:

I think you can use YTD calculation. It will work on the month selected, you will only need to select the latest month from slicer. In your case it could be April.

For current year:

CY_Value= You can use system generated YTD calculation (if you have date field your data) 

For previous year

PY_Value = CALCULATE(SUM(Query1[Reduktion i alt]),SAMEPERIODLASTYEAR('date_table'[Date].[Date]))

Once you have CY and PY value, you can use these values to arrive at percentage. Please let me know incase you are trying to achieve something else.