Forum Discussion

1845Sicem's avatar
1845Sicem
New Member
6 years ago

DAX Calculate % To Quota within Specified Time Frame

I need to calculate in a column the percentage of Quota Achieved YTD for Orders entered to create a bar graph. 

I have three tables (4) Tables where these are tied together:

 

tblManufacturerQuotas - Stores the associated quota requirement in $0.00 for a given year by Manufacturer

tblOS_MFCTR - Stores the $0.00 for total orders shipped for a given year.

tblManufacturers - Lists all the manufacturer information

DAXCALENDARTBL - Dax Generated Calendar Table.

 

I have no idea how to divide the tblOS_MFCTR[DSP] / tblManufacturerQuotas[Quota]  based on a specified year, either by filter or slicer. 

 

Here is a picture of the tables for the whole model and associations:

 

 

End Goal looks something like this which was generated via Excel Pivot Table Charts.

Need Help
Tutorial Requests

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Create a date table join with dates of your tables and the use time intelligence and date calendar to get YTD

    example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    • 1845Sicem's avatar
      1845Sicem
      New Member
      6 years ago

      FYI my current date table "DAXCALENDARTBL" uses the following:

      DAXCALENDARTBL =
      VAR BaseCalendar =
          CALENDARAUTO ( 12 )
      RETURN
          GENERATE (
              BaseCalendar,
              VAR BaseDate = [Date]
              VAR YearDate = YEAR ( BaseDate )
              VAR MonthNumber = MONTH ( BaseDate )
              VAR MonthName = FORMAT ( BaseDate, "mmmm" )
              VAR YearMonthName = FORMAT ( BaseDate, "yyyy-mm" )
              VAR YearMonthNumber = YearDate * 12 + MonthNumber - 1
      RETURN ROW (
                 "Day", BaseDate,
                 "Year", YearDate,
                 "Month Number", MonthNumber,
                 "Month", MonthName,
                 "Year Month Number", YearMonthNumber,
                 "Year Month", YearMonthName
              )
      )

       

      Is this sufficient? I'm using it with other functions in the file as well. I'll give your suggestion a go and see how it lands. I don't see though how I can divide the quota for Manufacturer X in year 2019 by tblOS_MFCTR[DSP] in 2019 to get a %.

      Thanks!

       
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Any chance you can change your relationship between tblManufacturers and tblOS_MFCTR to a direction of Both?

     

    Otherwise, probably need to use something like LOOKUPVALUE or MAXX(FILTER(...)...)