cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Measures with no start date

I am working with leger entries and normally I can user the following to filter out the dates I want.

..02/01/2024

This would show me all records with the end date less than Feb 1 2024

But how I am supposed to relate that into my measures so I can see this value by date?

I have a measure that doesn't factor in the posting date.

I have that table (value entry) linked to my MasterDate table.

When I put the measure and month into a chart i get this

I understand why I get this but I want to know is how do I get all date prior to that date into that measure?

The filters should be

Januaray 2023 = ..01/31/23

Feburary 2023 = ..02/28/23

etc

1 ACCEPTED SOLUTION
Super User

Better RT =
VAR __Date = MAX(MasterDate[Dates])
VAR __Table = FILTER(ALLSELECTED(MasterDate),MasterDate[Dates] <= __Date)
RETURN
SUMX(__Table,[Invoiced Value])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Helper I

I tried the calculate type and that worked

Better RT =
CALCULATE(
[Inventory Valuation],
FILTER(
CALCULATETABLE(
SUMMARIZE(
'MasterDate',
'MasterDate'[Dates].[MonthNo],
'MasterDate'[Dates].[Month]
),
ALLSELECTED('MasterDate')
),
ISONORAFTER(
'MasterDate'[Dates].[MonthNo], MAX('MasterDate'[Dates].[MonthNo]), DESC,
'MasterDate'[Dates].[Month], MAX('MasterDate'[Dates].[Month]), DESC
)
)
)
Helper I

That seems like what I need but I'm getting the same value each month now

I tried this code

Better RT =
VAR __Date = MAX(MasterDate[Dates].[Date])
VAR __Table = FILTER(ALLSELECTED(MasterDate),MasterDate[Dates].[Date] <= __Date)
RETURN
SUMX(__Table,[Invoiced Value])

My invoiced value measure is this
Invoiced Value = CALCULATE(SUM(itemLedgerEntry[costAmountActual]),valueEntry[locationCode]="KS")

This is my relationship between value entry and masterdate

and the relationship between item ledger entry and value entry

Super User

Better RT =
VAR __Date = MAX(MasterDate[Dates])
VAR __Table = FILTER(ALLSELECTED(MasterDate),MasterDate[Dates] <= __Date)
RETURN
SUMX(__Table,[Invoiced Value])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

Awesome. Thank you!

Super User

@bignadad Not sure I am tracking this. Are you saying you want a running total? If that is the case: Better Running Total - Microsoft Fabric Community

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors