Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Masure Sum based on string and date column

Hello,   i am facing an issue when i want to create a measure to sum values in one column when : values in one of the strings column equal to "Published Indicator" latest date in date column E...
Need Help
MFelix's avatar
MFelix
Super User
6 years ago

Hi  Anonymous ,

 

Try the following code:

Measure =
SUMX (
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[Date when data was loaded to database],
            Table1[Key Figure],
            "@Published", SUM ( Table1[Published] )
        ),
        Table1[Date when data was loaded to database] = "History"
            && Table1[Key Figure] = "Published Indicator"
            && (
                CALCULATE ( MAX ( Table1[Time Period Date] ), ALLSELECTED ( 'CAlendar'[Date] ) )
                    = MAX ( Table1[Time Period Date] )
            )
    )
    [@Published]
)

 

 

Also be aware that measures are calcuated with context, having the calendar table connected with your main table by the Table[TimePeriod] and Calendar[Date], you need to start using the Date as part of your visualizations and not the Time Period otherwise the calculations will get incorrect.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello,

 

measure again is giving an error. Not sure why it is so hard to filter by last date 😞

 

 

 

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    My bad lacking a separator on the formula before the [@Published]

     

    Measure =
SUMX (
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[Date when data was loaded to database],
            Table1[Key Figure],
            "@Published", SUM ( Table1[Published] )
        ),
        Table1[Date when data was loaded to database] = "History"
            && Table1[Key Figure] = "Published Indicator"
            && (
                CALCULATE ( MAX ( Table1[Time Period Date] ), ALLSELECTED ( 'CAlendar'[Date] ) )
                    = MAX ( Table1[Time Period Date] )
            )
    ),
    [@Published]
)