How to use the max() value of a DateTime Field

[Anonymous]

Hello!

Im trying to extract the second input of a DateTime box to create a new table which has only the time dates of that month time input (from start until the month end).

I tried this code but i got the december dates from 2024 (my input is january 2021)

day1 = CALCULATETABLE(Calendario,DATESBETWEEN(Calendario[Fecha].[Date], DATE(YEAR(max('Calendario'[Fecha].[date])),MONTH(max('Calendario'[Fecha].[date])),1),DATE(YEAR(max('Calendario'[Fecha].[date])),MONTH(max('Calendario'[Fecha].[date])),31)))

Thank you all!

 

[parry2k]

@Anonymous 2nd table is getting data from excel file, click transform data and you will see excel as a data source.

[parry2k]

@Anonymous so what is your question? The expression in the first table is used to create a date table and 2nd table is just a regular data table with a date column in it, like any other table in the model. Not sure what is your question or what is not clear?

[parry2k]

@Anonymous that is just a date column in the table, not sure what is your question?

[Anonymous]

Hii, 

the first table has a formula:

The second doesnt

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

According to your description, if you don't want to change the value in values (written with measure), it is very difficult to only change the x-axis dynamically. I have a workaound here.

You can create a measure,then use it in filter pane and use date hierarchy in x axis. Note that the slicer uses a date table.

Measure = 
VAR month =
    MONTH ( MAX ( 'Table'[Date] ) )
VAR year =
    YEAR ( MAX ( 'Table'[Date] ) )
VAR last =
    EOMONTH ( MAX ( 'Table'[Date] ), 0 )
VAR first =
    DATE ( year, month, 1 )
RETURN
    IF (
        SELECTEDVALUE ( Table1[date] ) >= first
            && SELECTEDVALUE ( Table1[date] ) <= last,
        1,
        0
    )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-janeyg-msft !.

Thank you for Your answer, its very useful.

I got a question. How did you create table1- date?. I dont understand it

[v-janeyg-msft]

Hi, @Anonymous 

You can download my sample file. The table is a calculate table,you can create whatever you want.

Like this:

Table = CALENDAR(MIN(table[date]),MAX(table[date]))

Table = CALENDARAUTO()

A date column will be automatically generated after creation.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

[parry2k]

Hey @Anonymous , sorry for not getting back to you. Why not share pbix file with the expected output, make a smaller sample data, and remove any sensitive information before sharing, share it using one drive/google drive.

[parry2k]

@Anonymous depends on what is your end goal, it will help to decide the best way to solve it.

[Anonymous]

I need a X axis. 

[Anonymous]

@parry2k help pls 😕

[parry2k]

@Anonymous you cannot use the slicer value in the calculated table.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hii, thanks for your answer.

So what can i do? I need to create a column with the days of 1 to 28/30/31 (depends of the month) ... this according with the 2nd input value given.