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

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

Reply
bignadad
Helper I
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

 

bignadad_0-1706888525534.png

 

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

@bignadad Best not to use the [Date].[Date] notation. Instead:

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
bignadad
Helper I
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
    )
  )
)
bignadad
Helper I
Helper I

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

 

bignadad_0-1706889797630.png

 

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
bignadad_1-1706889896573.png

and the relationship between item ledger entry and value entry

bignadad_2-1706889918248.png

 

@bignadad Best not to use the [Date].[Date] notation. Instead:

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Awesome. Thank you!

Greg_Deckler
Super User
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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.