Forum Discussion
Three Tables, YTD Calculation by FY
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
Hi Anonymous ,
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.
3 Replies
- amitchandak
Super User
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 Linkedinamitchandak 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.
Hi Anonymous ,
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.
