Unable to Calculate Row Count by Month Using a Date Table

[pinfsp18]

I have data that I need to show percentage by month by a field in my table.  I have 'ticket counts' by month by 'assigned to' that I need to chart.  Everything worked fine when I was using one year of data and no calendar table.  Now that the year has rolled over January tickets for both 2019 and 2020 display in the same month so I need to use a date table to break them out.  When I set the relationship from my date field in my data table to the date in the date table I lose the date hierarchy and formula no longer works.  Below is what works without using a calendar table, but I cannot figure out how to modify it to get total closed ticket by assigned to per month.  I tried replacing the 'PowerBI Ticket Data'[CompletedDate].[Month] with 'DateTable'[Date].[Month] to no avail.

% Mobility Overall =

CALCULATE (

    SUM ( 'PowerBI Ticket Data'[Mobility Tickets] ),

    ALLEXCEPT ( 'PowerBI Ticket Data', 'PowerBI Ticket Data'[CompletedDate].[Month], 'PowerBI Ticket Data'[Assigned to] )

)

    / CALCULATE (

        SUM ( 'PowerBI Ticket Data'[Mobility Tickets] ),

        ALLEXCEPT ( 'PowerBI Ticket Data', 'PowerBI Ticket Data'[CompletedDate].[Month] )

    )

[dax]

Hi @pinfsp18 , 

I think you don't need to create calendar table, you could use measure like below to see  whether it work or not. 

Measure 2 = SUM(T2[amount])/ CALCULATE(SUM(T2[amount]), ALLEXCEPT(T2,T2[date].[Year],T2[date].[Month]))

You could refer to my sample for details. If this is not waht you want, please correct me and inform me more detailed information (such as your sample data and your expected output).

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pinfsp18]

Thanks, but the solutions are not providing what I am looking for.  My DAX skills are weak at best, I am not looking for month to date or year to date and the like.  I am simply trying to pull the percentage each 'assigned to' person completed by month which worked before I added a date table which I now need to break out 2019 and 2020 data.  Below is an example of what I am trying to show.  When the Assigned To name is selected I want the line portion of the chart to show what percentage of the overall demand was completed by that Assigned To person each month. 

In May the team completed 320 tickets (127 SRs + 193 INCs)

The Assigned To selected completed 105 tickets (28 SRs + 77 INCs)

For May only that Assigned To persons completed 32.8% of the totals tickets in May (105/320)

[d_gosbell]

Typically adding a date table will simplify things allowing you to reduce the number of times you need to call ALLEXCEPT.

It's hard to be sure with out seeing some sample data (ie. I'm not sure why the ALLEXCEPT is needed on the numerator), But I would have thought something like the following might work:

% Mobility Overall =

DIVIDE( 

    SUM ( 'PowerBI Ticket Data'[Mobility Tickets] ),

    CALCULATE (

        SUM ( 'PowerBI Ticket Data'[Mobility Tickets] ),

        ALL( 'PowerBI Ticket Data'[Assigned To] )

    )

 PS. I'm also using the divide function instead of the numeric / operator as it automatically handles things like divide by zero without throwing errors.

[amitchandak]

As you have created date table. You can take advantage of time intelligence formula. Also you can create additional column in date table like month, year , month-year etc

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))


QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))



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

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



Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin