Forum Discussion
Measure Help
Hi there;
I need your little help, I have a table like as below I need to calculate with a measure only the sum of the amount of the current month and previous month
For the below example , as you see current month and previous dates are signed with yellow , and i need to capture total amount " 300"
But pls don't forget the main thing when you open the report, the system will compare the date of the opening report and find the amount of the current month and Previous month's amounts .
I Hope it's clear, thanks in advance
erhan_79
Try below measure:Measure = CALCULATE( SUM(Table1[Amount]), FILTER( ALL(Table1[Date]) , Table1[Date] <= EOMONTH(TODAY(),0) ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
7 Replies
erhan_79
Try below measure:Measure = CALCULATE( SUM(Table1[Amount]), FILTER( ALL(Table1[Date]) , Table1[Date] <= EOMONTH(TODAY(),0) ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
erhan_79 , One way with date table, cumulative till last month
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,dateadd(date[date]),-1,month)))
or last few months
Rolling 6 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-6,MONTH))
and this month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
dear amitchandak ;
can you check below formula , could be something wrong ? , something is not working.
erhan_79 I'm not sure I understand your question - are you trying to find a cumulative sum of everything including this month and before? Do you have a DimDate table? You will need one with a Date data type column, and month column.
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
There are a few options here. You could filter this with a relative date slicer or use an explicit measure with filter inside the measure, such as:
AmountToDate =
CALCULATE(SUM(Table[Amount]), MONTH(DimDate[Date]) <= MONTH(TODAY()) && YEAR(DimDate[Date]) = YEAR(TODAY()) )
dear AllisonKennedy ;
i dont have dim dat table , i am working on a live data so i can not create dim date .Is not possible to solve without Dimdate ?
yes ı am trying to find a cumulative sum of everything including this month and before
thanks
