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 Miguel,

 

Power Bi is flagging multiple errors due to semicolons

 

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

Hi Anonymous ,

 

This is related with regional settings on the computer replace the semicolons by commas and it will work accept the code:

LWPublishedv1 =
CALCULATE (
    SUM ( Table1[Published] ),
    FILTER (
        'Table1',
        'Table1'[Key Figure] = "Published Indicator"
            && (
                CALCULATE ( MAX ( Table1[Time Period Date] ), ALL ( 'CAlendar'[Date] ) )
                    = MAX ( Table1[Time Period Date] )
            )
                = TRUE ()
            && 'Table1'[Date when data was loaded to database] = "History"
    )
)
  • 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
    Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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]
)