Hello
I have a table of transactions, (ReflectReport), with multiple criteria columns. I have added a table column, (Fin Year) using
=IF(AND(MONTH([@Date])=MONTH(TODAY()),YEAR([@Date])=YEAR(TODAY())),"2","1")
1 equals last financial year and 2 equals this financial year.
Other columns, but not all, are:
Month uses: =IF([@Date]="","",CHOOSE(MONTH([@Date]),7,8,9,10,11,12,1,2,3,4,5,6))
Fin Year Month uses: =IF([@Date]="","",TEXT([@Date],"mmm"))
Calendar Month uses: =IF([@Date]="","",MONTH([@Date]))
I have another table, (TblActivity) with a column, "Spent This Month", that calculates spent this month by activity and schedule using:
=IFERROR(SUMIFS(ReflectReport[Accomplishment Cost],ReflectReport[Schedule],[@Schedule],ReflectReport[[Calendar Month ]],MONTH(TODAY()),ReflectReport[Activity],[@Activity]),"Check Dates")
I need this formula to use column "Date" to differentiate between the months of financial years, (and all the other criteria).
At the moment it is adding July 2020 and 2021 together.
Any help would be very much appreciated.
Thanks
Steve
I have a table of transactions, (ReflectReport), with multiple criteria columns. I have added a table column, (Fin Year) using
=IF(AND(MONTH([@Date])=MONTH(TODAY()),YEAR([@Date])=YEAR(TODAY())),"2","1")
1 equals last financial year and 2 equals this financial year.
Other columns, but not all, are:
Date | Accomplishment Cost | RM | Month | Fin Year Month | Calendar Month | Fin Year |
03/Jul/2021 | 1,569.00 | 1 | Jul | 7 | 2 | |
03/Jul/2020 | 842.50 | 1 | Jul | 7 | 1 | |
06/Jul/2020 | 1,569.00 | 1 | Jul | 7 | 1 | |
06/Jul/2020 | 842.50 | 1 | Jul | 7 | 1 |
Fin Year Month uses: =IF([@Date]="","",TEXT([@Date],"mmm"))
Calendar Month uses: =IF([@Date]="","",MONTH([@Date]))
I have another table, (TblActivity) with a column, "Spent This Month", that calculates spent this month by activity and schedule using:
=IFERROR(SUMIFS(ReflectReport[Accomplishment Cost],ReflectReport[Schedule],[@Schedule],ReflectReport[[Calendar Month ]],MONTH(TODAY()),ReflectReport[Activity],[@Activity]),"Check Dates")
I need this formula to use column "Date" to differentiate between the months of financial years, (and all the other criteria).
At the moment it is adding July 2020 and 2021 together.
Any help would be very much appreciated.
Thanks
Steve