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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello,

 

thank you for supporting on the topic. Unfortunatelly the new formula is giving same result as previous one. The Key Figure criteria is properly working ( it is summing only when Key Figure ="Published Indicator") but the date criteria is not working :

 

 

 

File : https://easyupload.io/k0xo1j

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]
)