Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Getting my feet wet with a dashboard and looking for YTD expenses for Material cost category. I have a table of data - example below:
Cost Category | Month | Monthly Expense |
Total Personnel | January | $ 2,000 |
Total Personnel | February | $ 2,000 |
Total Personnel | March | $ 2,100 |
Total Personnel | April | $ 2,000 |
Total Personnel | May | $ 2,100 |
Total Personnel | June | $ 2,100 |
Total Personnel | July | $ 2,200 |
Total Personnel | August | $ 2,200 |
Total Personnel | September | $ 2,200 |
Total Personnel | October | $ 2,200 |
Total Personnel | November | $ 2,220 |
Total Personnel | December | $ 2,200 |
Total Material | January | $ 700 |
Total Material | February | $ 700 |
Total Material | March | $ 350 |
Total Material | April | $ 350 |
Total Material | May | $ 400 |
Total Material | June | $ 400 |
Total Material | July | $ 400 |
Total Material | August | $ 400 |
Total Material | September | $ 350 |
Total Material | October | $ 375 |
Total Material | November | $ 375 |
Total Material | December | $ 350 |
Jan-Jun data is actual and Jul-Dec data is forecasted. I am looking for YTD cumulative expense. For a bar chart, the following expression works great:
rangesum(above(Sum({<[Cost Category]={'Total Material'}>} num(trim([Monthly Expense]))),0,rowno()))
This shows the cumulative total by month. I am trying to use something similar to show how much I spent on Material through June (or whatever month I decide). Any help would be appreciated.
using GetFieldSelections in the chart could work. (selecting the required month through the filter pane )
Yes, that does work - thank you. But what I'd like it to do is calculate the YTD automatically based on the date. So, if today is July 18th, it calculates Jan-Jun. If it is September, then it calculates Jan-Aug. I am using the gauge chart and was trying to get the amount spent as of the last full month.
then a something like this could work with set analysis for calculating the year as year(today()) and months as month(today())-1
rangesum({< [Cost Category]={'Total Material'}, [Year]={'$(=year(today()))'}, [Month]={'<=$(=month(today())-1)'}>}[Monthly Expense])
I'm still a beginner with set analysis, hope this works.
Thanks again - yes, set analysis is the way to go. This doesn't quite work - I think it will work if I have another column that translates the month string names (January, Febraury, etc) into their corresponding numbers (1,2...12).
num(month(yourDATE)) also would work to get 1-12. but yes, easier if there's a field already.