Measure Help

[erhan_79]

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

[Fowmy · Accepted solution]

@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 🙂

YouTube  LinkedIn

[erhan_79]

thank you very much @Fowmy 

[AllisonKennedy]

@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()) )

[erhan_79]

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 

[AllisonKennedy]

@erhan_79 , Yes it is possible without date table, just use the Date column from your table instead of DimDate table. Note my solution assumes you only want data from this year. See the solution from @Fowmy  for a more elegant solution if you want ALL data from all previous years up to this month.

[amitchandak]

@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.

[erhan_79]

dear @amitchandak ;

can you check below formula , could be something wrong ? , something is not working.