Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
virus190
Helper II
Helper II

EOMonth -1 same logic different results

Hi,

 

i'm stuck because of this problem, tried all day. Please help.

For example i entered data manually in Power Bi, lets say i have this table:

 

Table.PNG

Now i want the Sum of Sales until the last full month (29.02.2020). So the result should be 2.100

 

My first try was 2 Measures (Auto):

LastFullMonth Auto = 
VAR
MaxDate = MAX(Sales[Date])
VAR
MaxDateEO = EOMONTH(MaxDate;0)
VAR
MaxDateEOLM = EOMONTH(MaxDate;-1)
VAR
LastFullMonth = IF(MaxDate=MaxDateEO;MaxDate;MaxDateEOLM)
Return
LastFullMonth
Sales Until LastFullMonth Auto = CALCULATE(SUM(Sales[Sales]);FILTER(Sales;Sales[Date]<=[LastFullMonth Auto]))

Result is not what i expected, why 600 ??

Result Auto.PNG

 

To understand it, i tried a manually way:

LastFullMonth manually = DATEVALUE("29.02.2020")
Sales Until LastFullMonth manually = CALCULATE(SUM(Sales[Sales]);FILTER(Sales;Sales[Date]<=[LastFullMonth manually]))

Result is as expected:

Result manually.PNG

 

So for me its the same logic, even the date is the same, but why is the result different? I need it the automatic way..

 

Power Query Source Code:

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcvLCcAwDATRXvZswkqK86lFuP82EoNsFub0YDJBO/6cTjQYidES1gW9MCgYhXP3hafsG3uhv4KX7LHwln3jM3F8", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Sales = _t]),
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Date", type date}, {"Sales", Int64.Type}})
in
    #"Geänderter Typ"

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Here's how to (kind of) fix it:

Sales Until LastFullMonth =
var __lastFullMonthDate = [Last Full Month Date]
return
CALCULATE (
    [Total Sales],
    keepfilters( Sales[Date] <= __lastFullMonthDate )
)

But please note that the model is flawed, so I would not advise you to proceed in this manner unless you are prepared to spend countless hours on finding bugs. Many of them you'll not even notice.

 

Best

D

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Here's how to (kind of) fix it:

Sales Until LastFullMonth =
var __lastFullMonthDate = [Last Full Month Date]
return
CALCULATE (
    [Total Sales],
    keepfilters( Sales[Date] <= __lastFullMonthDate )
)

But please note that the model is flawed, so I would not advise you to proceed in this manner unless you are prepared to spend countless hours on finding bugs. Many of them you'll not even notice.

 

Best

D

thanks for the quick response!

 

I do now understand the difference.

 

But i dont understand why the model is bad and what is a better model?

Anonymous
Not applicable

A good model has a dedicated calendar. You should never directly use a date that's in your fact table. More, you should never ever slice a fact table directly. Only through dimensions. A fact table should always be hidden.

Best
D

thanks for explanation. 

 

the data showed was only example to simply show the problem. It's not my model that im using.

I do have a dedicated calendar table and of course using it for filters. 

But sure its not perfect, i will look up to optimize it 😉

 

Thanks!

Anonymous
Not applicable

@virus190, when you refer problems, it's good to replicate your real model as much as possible here (or give a hint about what it looks like). Because then solutions will be immediately applicable. When code is written against a different model than yours... well, the solution might not work correctly in the true one. For instance, you write different code against a model with bi-dir filtering and different against one without it.

Best
D
Anonymous
Not applicable

Here's why you see 600 when you use the measure.

 

Your measure is:

Sales Until LastFullMonth manually =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
    	Sales,
    	Sales[Date] <= [LastFullMonth Auto]
    )
)

FILTER is an iterator, so it iterates the table Sales but it iterates what? The rows as visible in the current context. When this formula is evaluated in the grand total [LastFullMonth Auto] changes together with the row because of context transition - from row context into filter context. We have to figure out what the value of the measure is for each and every row in Sales. Then you'll know why you only see the value from the last day of the full month.

 

This measure

LastFullMonth Auto =
VAR MaxDate =
    MAX ( Sales[Date] )
VAR MaxDateEO =
    EOMONTH ( MaxDate, 0 )
VAR MaxDateEOLM =
    EOMONTH ( MaxDate, -1 )
VAR LastFullMonth =
    IF ( MaxDate = MaxDateEO, MaxDate, MaxDateEOLM )
RETURN
    LastFullMonth

does the following. It grabs the date from the currently iterated row in Sales (as visible in the context) and assigns it to MaxDate. Then it finds the EOM date for MaxDate. Then from MaxDate the last day of the prior month is calculated. LastFullMonth is then - most of the time - MaxDateEOLM, because it's unlikely that the current date in the row will be equal to the last date in the month. When this happens, the condition is evaluated and most of the time the row will not qualify because MaxDateEOLM < MaxDate. Only the row where MaxDate = MaxDateEO will qualify and it so happens that it'll be the last day of the month. There's only one such day - 29.02.2000. If you change the 3rd row's date to 31.01. 2020, the sum will be 900 (600 + 300).

 

So that's the secret.

 

Best

D

Anonymous
Not applicable

No. It's not the same logic. Measures are dynamic. Static values are... static. Measures get evaluated in the current context. And measures perform what's known as context transition.

I'd warn you against using the model design you have just showed. Unless you want to be in for a lot of surprises.

Best
D

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors