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 your answer. Unfortunatelly this is not what i need to achieve. 

 

In a nutshell, what i need is a measure that will sum "Published" column when KeyFigure column is "Published Indicator" for last week from History (Date when added to database = History), Time Period = MAX ). 

 

In the file you shared the measure is summing not only for the last week but also for past weeks:

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

Hi  Anonymous ,

 

Try the following 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 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