Masure Sum based on string and date column

[Anonymous]

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 :

Id	Time Period Date	Keyfigure	Published
12345678	3/14/2020	ManualOverwrite	0
12345678	3/14/2020	IncrementalValue	0
12345678	3/14/2020	PublishedIndicator	0
12345678	3/14/2020	ReviewFlag	0
12576423	3/7/2020	ManualOverwrite	1
12576423	3/7/2020	IncrementalValue	0
12576423	3/7/2020	PublishedIndicator	1
12576423	3/7/2020	ReviewFlag	0
26534665	2/29/2020	ManualOverwrite	1
26534665	2/29/2020	IncrementalValue	0
26534665	2/29/2020	PublishedIndicator	1
26534665	2/29/2020	ReviewFlag	1

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 ? 

[v-gizhi-msft]

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]

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]

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]

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]

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]

Hello Miguel,

Power Bi is flagging multiple errors due to semicolons

[MFelix]

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]

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]

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]

Hello,

measure again is giving an error. Not sure why it is so hard to filter by last date 😞

[MFelix]

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

[MFelix]

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.