cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thatjoey20 Frequent Visitor
Frequent Visitor

How to use specific dates as a dimension in a table

Hello guys,  

I am struggling to show the correct dates of  a measure that represents the value of 12 followed months of the first year registered in the date table, for instance: I have dates registired of  1/2019 until 1/2016, and this measure only calculates values of 2019 until 2018. But when I put this measure and the date column in a single table, the column of the date shows the values of the measure even out of the period I specified in the measure, here is the measure above


LAST12MONTHS = CALCULATE([VALUE];
DATESBETWEEN('Bills'[Date];
DATEADD(SAMEPERIODLASTYEAR(LASTDATE('Bills'[Date]));1;MONTH);
LASTDATE('Bills'[Date])))

The measure is calculating correctly the value between these dates, but when I drag this measure and the date column in the same table, the date column doesn't respect the measure and brings dates out of the measure condition. 
 
How can I make it respect the measure? Like only showing the dates that are being calculated in the measure

 

3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: How to use specific dates as a dimension in a table

@thatjoey20 ,

Do you mean that you wants to get the values of last 12 months based on registered date, in this scenario, we can create a calendar table using dax(Table 2= Calendar("1/1/2016","12/31/2019")) and then create a measure as below. After that, drag date field of bill table and the measure in a table visual.

LAST12MONTHS = 

var thisdate = LASTDATE('Table 2'[Date])

var lastyeardate = DATEADD(LASTDATE('Table 2'[Date]),-1,YEAR)

return

CALCULATE(SUM(Bills[yourfield]),

DATESBETWEEN(Bills[Date],lastyeardate,thisdate))

 

If the above DAX doesn’t help, could you please share the target result to us? You can share some sample data and target result as an example.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thatjoey20 Frequent Visitor
Frequent Visitor

Re: How to use specific dates as a dimension in a table

I tried using that, but the measure itself brings an error saying that a MONTH,YEAR, QUARTER is necessary as a argument

Moderator v-yuezhe-msft
Moderator

Re: How to use specific dates as a dimension in a table

@thatjoey20 ,

Would you mind sharing your PBIX file containing dummy data?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.