Three Tables, YTD Calculation by FY

[MikeMCFRS]

Hi All,

I have a date table, Disposition table and Incident number table. A sample is decribed as follows:

Date Table	Disposition Table	Incident Table
01/01/2020	Transports	Incident number(Column)
01/02/2020	Refusals	Incident date(column)
01/03/2020	Treated
01/04/2020	Discharged

 I have already linked all three tables. The goal or outpuut I am looking for is to

a. Calculate monthly totals by category in the disposition table, eg Total Transports

b. Calcluate YTD

C. When I place the measures on the card visual forexample, I want to be able to see Month Total & YTD for each category

e.g Month          YTD
       25                 75

I have already set up the FY in the date table and the visual, however I am not able to create a formular that will be consistent in producing the desired results as shown above.

Please NOTE: The disposition and Incident tables did not have values so I created a count value of 1 for each incident. The disposition table does not have a date column so I linked it to the date table.

Any help will be greatly appreciated. I am open to your questions for further clarity.

Thanks

[v-jayw-msft · Accepted solution]

Hi @MikeMCFRS ,

Refer the measures below and see if it help.

monthly totals by category = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[category]),FORMAT('Table'[date],"MM")=FORMAT(SELECTEDVALUE('Table'[date]),"MM")))

YTD = CALCULATE(SUM('Table'[value]),FILTER(ALLEXCEPT('Table','Table'[category]),'Table'[date]<=MAX('Table'[date])))

 Result:

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

Using the date table and time intelligence,  you can create MTD and YTD measures. In ytd you can mention year end date to take care of your financial year.

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))))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('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"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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 Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[MikeMCFRS]

@amitchandak  Many thanks for your suggested formulas. My problem is not with the finding a formula per se but one that will work for my specific scenario. I am trying to count categories in one table(Disposition) with another table( incident numbers) in another table and also creating YTD formular for each category.

Hope this clarifies my dilemma a little bit.