Forum Discussion

erhan_79's avatar
erhan_79
Icon for Post Prodigy rankPost Prodigy
6 years ago
Solved

need formula

Hi there ;   i need your help to create below formula    i have a table as below , calculating rule will be : for each actual month ( acual month is august today) , system will find first day and...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi erhan_79 ,

    According to my understanding, you want to calculate the sum of the first day’s(in table column) amount of current month , right?

    You could use the following formula:

    sum =
VAR _first =
    CALCULATE (
        MIN ( 'SumFirstDayMonth'[Date] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Status] = "Actual Month" )
    )
RETURN
    CALCULATE (
        SUM ( SumFirstDayMonth[Amount] ),
        FILTER ( ALL ( SumFirstDayMonth ), 'SumFirstDayMonth'[Date] = _first )
)

    My visualization looks like this:

    Is the result what you want? If not, please upload some data samples and expected output.

    Please do mask sensitive data before uploading.

     

    Best Regards,

    Eyelyn Qin

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

erhan_79 , better to do it with month year, try a new column like

 

New Status =
var _min = minx(filter(Table, format([Date], "YYYYMM") =format( earlier([Date]), "YYYYMM") && eomonth(today(),0) = eomonth([Date],0)),[Date])
return
if([Date]=_min, "First Date of Month",[Status])

 

or a new measure

 

New Status =
var _min = minx(filter(Table, format(Table[Date], "YYYYMM") =format( max(Table[Date]), "YYYYMM") && eomonth(today(),0) = eomonth([Date],0)),[Date])
return
if([Date]=_min, "First Date of Month",[Status])

 

better to have Month year column and use that, to compare months

Month year = format(Table[Date], "YYYYMM")

 

example

New Status =
var _min = minx(filter(Table, ([Month Year]) =format( earlier([Month Year])) && eomonth(today(),0) = eomonth([Date],0)),[Date])
return
if([Date]=_min, "First Date of Month",[Status])