Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am sure that this is really rudimentary, but what's wrong with this formula?
=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]=month(today())>}[Actuals.FIN])
I'm adding actuals for the whole year and deducting current month's actuals.
Hi,
the red part is the wrong part
=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]=month(today())>}[Actuals.FIN])
try it like this:
=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today()))'}>}[Actuals.FIN])
check if your field [Month Num] is same Format as month(today())
month(today()) gives back string like "Aug". If [month Num] contains number you should use date(today(),'MM')
Hi,
the red part is the wrong part
=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]=month(today())>}[Actuals.FIN])
try it like this:
=Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN]) - Sum({<[Year]={$(vCurrentYear)},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today()))'}>}[Actuals.FIN])
Thank you! this really helps
Continuing to build on this - Now what am I doing wrong? The first part of the formula obviously works, it's the if statement that doesn't seem to work.
If the date is less than 15 deduct the total of current month and previous month if greater than 15 then just current month from the total for the year.
Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1}>}[Actuals.FIN])
- if(date('$(today())','DD')>15,Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+0)'}>}[Actuals.FIN]),
Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+0)'}>}[Actuals.FIN]) + Sum({<[Year]={$(vCurrentYear)},[Major Category]={'Project Costs'},[%RED_IT_FINANCIAL_ALL]={1},[Month Num]={'$(=month(today())+1)'}>}[Actuals.FIN]))