Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Visualize partial months in bar chart

I have sales data that goes through December 21st 2021 I also have last year's sales data through December 21st 2020.  Note that I blanked out all data past todays (12/21) date as I want to compare a...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , Try datesytd with additional filter

 

YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))


YTD QTY forced=
var _max = today()//or maxx(allselected('Order'),'order'[Date])
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

 

LYTD QTY forced=
var _today = maxx(allselected('Order'),'order'[Date])
var _max = date(year(_today)-1,month(_today),day(_today))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//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)

 

 

Alternate options in comments

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for getting back to me. I appreciate it.  I tried to create a measure utilizing the above with no luck.  I am certain it is because I don't fundamentally understand it. 

    My current measure is 

    1. CY GWP = if(LASTDATE(Dates[Date])>TODAY(),BLANK(),CALCULATE(SUM('2. Data for all Departments'[3. GWP])))
     
    GWP is nothing more than Sales. 
     
    I am using Last Years Sales formula as follows:
     
    1. LY GWP = if(LASTDATE(Dates[Date])>TODAY(),BLANK(),CALCULATE(SUM('2. Data for all Departments'[3. GWP]),DATEADD(Dates[Date],-1,YEAR)))
     
    The below is the bar chart I want to move out to December and reflect sales to date. (note below is just an item count, but wanted to illustrate what the bar graph is showing.  
     

     

     
    Pretty simple, but just want to reflect a partial month for December rather than cutting the bar graph off at November, which is obviously the last full month of data.