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

Example dataset :

IdTime Period DateKeyfigurePublished
123456783/14/2020ManualOverwrite0
123456783/14/2020IncrementalValue0
123456783/14/2020PublishedIndicator0
123456783/14/2020ReviewFlag0
125764233/7/2020ManualOverwrite1
125764233/7/2020IncrementalValue0
125764233/7/2020PublishedIndicator1
125764233/7/2020ReviewFlag0
265346652/29/2020ManualOverwrite1
265346652/29/2020IncrementalValue0
265346652/29/2020PublishedIndicator1
265346652/29/2020ReviewFlag1

 

I calculated the latest available date in separate measure :

 

 

 

 

LastWeek = CALCULATE(MAX('Table1'[Time Period Date]),'Table1'[Date when data was loaded to database]="History")

 

 

 

 

And then i tried :

 

1) 

 

 

 

LWPublishedv1 = CALCULATE(SUM('Table1'[Published]),'Table1'[Key Figure]="Published Indicator" && 'Table1'[Time Period Date]=[LastWeek])

 

 

 

Resulting with error :The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

2)

 

 

 

LWPublishedv2 = 
VAR LW=MAX('Table1'[Time Period Date].[Date])
VAR LwPub=CALCULATE(SUM('Table1'[Value]),FILTER('Table1','Table1'[Key Figure]="Published Indicator"&& 'Table1'[Time Period Date].[Date]=LW))
RETURN
    LwPub

 

 

 

Resulting with returned value being (Blank)

3)

 

 

 

LWPublishedv3 = 
VAR LW=MAX('Table1'[Time Period Date].[Date])
VAR LwPub=CALCULATE(SUM('Table1'[Value]),FILTER(FILTER('Table1','Table1'[Time Period Date]=LW),'Table1'[Key Figure]="Published Indicator"))
RETURN
    LwPub

 

 

 

Resulting with the returned value being (Blank)

 

Can you help me understand where i am making the mistake ? 

 

Need Help

12 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Can you share a sample file? Although you have a good presentation of the model I have some doubts so I can help you.

     

    You refer to a Table1'[Date when data was loaded to database] column I don't that in the mockup data you present.

     

    Another question is what is the result you want to have in the case of this calculation you also place a value column but that is not on your data.

     

  • v-gizhi-msft's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try this instead of your first formula:

    LWPublishedv1 =
CALCULATE (
    SUM ( 'Table1'[Published] ),
    FILTER (
        'Table1',
        'Table1'[Key Figure] = "Published Indicator"
            && 'Table1'[Time Period Date] = [LastWeek]
    )
)

    If you still have any issue, please share the expected result as a screenshot here.

    Expect your reply!

     

    Best Regards,

    Giotto Zhi

     

    • 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
        Super 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.