CODEWITHSUNDEEP
powerbidax
Serge Inácio
Serge Inácio

Reputation: 1382

Last date end of last month (last day of last month is not the end of the month)

I have a table, something like the following (just adding the dates as I think the rest is not relevant for this question):

Date
20/10/2023
10/10/2023
06/10/2023
01/10/2023
29/09/2023
16/09/2023
09/09/2023

As you can see the last day of the month is not the endo of the month.

I need to create a measure, something like the following:

myMeasure = 
    
VAR lDate = CALCULATE( LASTDATE('MyTable'[Date]), PREVIOUSMONTH(DateTable[Date]))

RETURN

CALCULATE(
    COUNT('Tickets'[ID]),
    DateTable[Date]=lDate
 )

The issue I am having with this is that it seems to display correctly when adding the Date from the DateTable and the measure to a table visual, but when adding the measure to a card it displays a (Blank) value.

What I want to do is each day to compare with the last available date of the previous month. So:

So if I select any of the folowing dates from my filter: 2

It would always return the value from the date:

Thank you in advance for any help.

Upvotes: 0

Views: 1016

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Try the following:

myMeasure =
  var lDate = CALCULATE( LASTDATE('MyTable'[Date]), PREVIOUSMONTH(ENDOFMONTH(DateTable[Date])) )
  RETURN
    CALCULATE(
     COUNT('Tickets'[ID]),
     FILTER(ALL('DateTable'[Date]), 'DateTable'[Date] = lDate)
    )

For it to work in a Card visual (or any visual), it needs a date context to know which prev month to look at. If you have a date slicer or date filter on the page, then it will work. If you don't then you can add a date filter to the card visual filter pane, you could use Relative date filtering with is in this month.

Upvotes: 0

Related Questions