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

 

thank you for replying. I tried the formula but unfortunatelly it seems that the date filter is not working properly. The measure is calculating for all of the dates.

 

In the link file from which screenshot was taken  :   https://easyupload.io/l12f1a 

 

 

 

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

Hi  Anonymous ,

 

Try to create calendar table and relate it with the Time period then adjust your Last week for the calendar table.

 

Check the PBIX file attach and tell me if this is the expected result.

  • 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