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 apples to apples.  The table looks fine and I have last year's sales by month (November last full month) and December is showing day data to the 20th.  I would like to have a bar chart showing all the completed months and the month to date for December in the same bar chart.  Right now my last bar on chart is November as it is the last complete month.  Any way to force power bi visualization to include December month to date and add up this year vs last year through the 20th? 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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.  
       
       
  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    Try these measures

    Sales TY = CALCULATE(SUM('Table'[Sales]),Dates[Year]=YEAR(TODAY()))
    Sales LY = 
VAR _month = SELECTEDVALUE(Dates[Month])
RETURN
CALCULATE(SUM('Table'[Sales]),ALL(Dates),Dates[Year]=YEAR(TODAY())-1,Dates[Month]=_month,Dates[Date]<=EDATE(TODAY(),-12))

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.