Forum Discussion

bignadad's avatar
bignadad
Icon for Helper I rankHelper I
2 years ago
Solved

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 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

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

5 Replies

  • bignadad's avatar
    bignadad
    Icon for Helper I rankHelper I
    2 years ago

    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

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      2 years ago

      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])
  • bignadad's avatar
    bignadad
    Icon for Helper I rankHelper I
    2 years ago

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