Time Intelligence

[VPlorim]

I am trying to create a measure that is the last 30 days with dates between today -1 and today -31 for a month of sales.

When I try to enter the total SV field in my table it is not shwoing as an option. The only options to choose from are other measures that I careated.

Is there something that I am doing wrong?
Thank you

I am using this formula

last 30 days = calculate(totalSV), datesbetween, (partiesandSV[partydate], today()-1, today()-31))

My issue is I cannot get the totalsv in the table to appear so I can choose that.

[Ashish_Mathur]

Hi,

The measure should be:

Measure 1 = calculate([totalSV],datesbetween(Calendar[Date],today()-1,today()-31))

There should be a calendar Table and a relationship from the Party Date column of the partiesandSV Table to the Date column of the Calendar Table.

Hope this helps.

[v-frfei-msft]

Hi @VPlorim ,

Refering to the online document, we should create a table to work on it.

date = calendarauto()

after that, create relationship between date table and your fact table, then we can update your measure as below.

last 30 days = calculate(totalSV), datesbetween, (date[date], today()-1, today()-31))

[VPlorim]

Hi there

I am not sure what I am doing wrong.  I created a date table using the formula below.  when I put in the one you suggested it came back with an error.

The one below created a date table and I created a relationship.

Then when i go to do the sales within 30 days only previously created measures are showing still.

master calendar =

  GENERATE (

    CALENDAR ( DATE ( 2000, 1, 1 ), DATE ( 2022, 12, 31 ) ),

    VAR currentDay = [order]

    VAR day = DAY( currentDay )

    VAR month =  MONTH ( currentDay )

    VAR year =  YEAR ( currentDay )

  RETURN   ROW (

    "day", day,

    "month", month,

    "year", year )

[v-frfei-msft]

Hi @VPlorim ,

What is [order] in your formula? Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[kcantor · Accepted solution]

@VPlorim 

Have you created a measure for Total SV?

I would suggest creating a measure for that and using it in the calculate or adding in the function to your existing calculation. It looks like you are using a naked column reference. Also, you need a dedicated date Table. Mine is called DimDate and the datekey is my key. I have subbed this into your measure.

Your measure: last 30 days = calculate(totalSV), datesbetween, (DimDate[datekey], today()-1, today()-31))

Try adding SUM so: last 30 days = calculate(SUM(TableName[totalSV]), datesbetween, (partiesandSV[partydate], today()-1, today()-31))

[Anonymous]

In order to use the time intelligence functions you need a dedicated date table.  Doesnt appear you have one here, is that correct? If it is, first step would be creating a calendar table. Easy to do in DAX using the Calendar Function