Get the days of Current Month

[Anonymous]

I have a column "Days of Month" which is just rows from 1 to 31 and a filter above with the date. I want to filter this column to show me the days of each month based on the filter above. For example if the date is 2/2/2020 i would like to see 28 rows on my column. I tried several solutions but i couldn't achieve this.

[rohit_singh]

Hi @Anonymous ,

Please see solution below ;

1) Load data into Power Query

Create a table with a single column named "Days of the month" that has day values from 1 to 31

2) In your date table, add a new column to calculate last day of the month using DAX.

Last Day of month = DAY(ENDOFMONTH(dim_date[Date]))

3) Create a mesaure called "last day flag". Capture the value of the last day of the month based on selected date in the slicer, and use that value to filter the days of the month table.

last day flag =

var _lastday = SELECTEDVALUE(dim_date[Last Day of month])

var _days = IF (SELECTEDVALUE('Days of the month'[Days of the month]) > SELECTEDVALUE(dim_date[Last Day of month]), 0 ,1)

RETURN

_days

4) Add the column for days of the month to a table visual. Use the date value from the date table as a slicer. On the table visual, add the measure 'last day flag' as a filter and set it to 1. You will get the result as shown below

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Hi @rohit_singh  thanks for you awnser, the problem here is that in the slicer i have to use a range of dated (between option) instead of list and if i implement the filter in the visual i get an empty table. 

[rohit_singh]

Hi @Anonymous ,

This is beacuse this mesaure will not be able to handle multiple values. If you select values from a between slicer, say two dates in different months for instance, such as 22/02/22 and 01/03/22, there will be two values for number of days. Feb has 28 or 29, and March as 31. 
Can you confirm that both values in the slicer will be from the same month?

Kind regards,

Rohit

[Anonymous]

No unfortunately the values is from different month the starting value is always 14/8/2018. 

[rohit_singh]

So what is your expected output if two dates from seperate months are selected?

[Anonymous]

@rohit_singh  to filter the col according to last date, the first date will always remain the same sorry if i am not mentioned it before. 

[rohit_singh · Accepted solution]

Thanks for your response @Anonymous . In that case please make the following changes to your measure that was created in step 3 in my answer above :

last day flag =

var _maxsel = MAX(dim_date[Date])

var _lastday = CALCULATE(MAX(dim_date[Last Day of month]), FILTER(dim_date, dim_date[Date] = _maxsel))

var _days = IF (SELECTEDVALUE('Days of the month'[Days of the month]) > _lastday, 0 ,1)

RETURN
_days

The _maxsel variable captures the max selected value on the slicer, and filters the date table to return the last day of the month associated with that date. 
This last day value is then compared against each row in the days of the month table. We set a flag of 1 when the value on the table is less than or equal to the last day value.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!  😊

[Anonymous]

Yes this was exactly what i wals looking for. Thanks a lot 🙂 

[Anonymous]

@rohit_singh Hi again, sorry that i reopen the topic but i have a problem. The column days of month is in the same table with the date let's say in the "dim date" table and is the code of each day and when i am trying to implement this kind of solution is not working. 

To give you an i idea the table is like this