Forum Discussion
Total Monthly Cost
I'm trying to create a new table that calculates the monthly total cost. I'm unsure on how to do this after looking online. Can anyone help with this? Below is a small snippet of my existing tables.
I would love for the table to add all the January costs, February costs, and so on so forth. i'm asssuming the table would only have 1 row per month.
Thanks in advance for the help!
Anonymous , You can create a date table with join with due date or date complete (or both, one inactive join)
and use date table show month-wise data
Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)If you joined both and one join is inactive, activate that in a measure using userelationship
calculate( SUM(Table[Total Cost]),USERELATIONSHIP ('Table'[DueDate], 'Date'[Date]))
calculate( SUM(Table[Total Cost]),USERELATIONSHIP ('Table'[Date completed], 'Date'[Date]))
refer
1 Reply
- amitchandak
Super User
Anonymous , You can create a date table with join with due date or date complete (or both, one inactive join)
and use date table show month-wise data
Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)If you joined both and one join is inactive, activate that in a measure using userelationship
calculate( SUM(Table[Total Cost]),USERELATIONSHIP ('Table'[DueDate], 'Date'[Date]))
calculate( SUM(Table[Total Cost]),USERELATIONSHIP ('Table'[Date completed], 'Date'[Date]))
refer