Edit query to show MTD values

[Anonymous]

Hello everybody!

I need to edit all the graphs that I have easily by clicking on a switch "MTD".

I thought to create a filter with just one checkbox "MTD". By clicking it all graphs show me the MTD values, otherwise all the other values aka YTD.

The only problwem is that I don't have the MTD info, and moreover I want it to be automated. In excel I would easily make a combination of function, where if the month in the date is the current one, it gives me MTD, otherwise nothing.

Is there a way to create this editing the query in DAX format?

Thx in advance.

Cheers,

Andrea

[tringuyenminh92]

Hi @Anonymous,

You could follow up these topic to achieve that expectation:

• https://community.powerbi.com/t5/Community-Blog/Dynamically-change-the-information-within-a-visual-via-a-slicer/ba-p/87027
• https://community.powerbi.com/t5/Desktop/Time-selector/td-p/96513

The idea is based on switch/if else method and return the expression that calculated YTD or MTD or another result

[Anonymous]

Hi @tringuyenminh92 and @parry2k.

Thanks a lot or your tips bt I don't really see them matching my case.

I'm almost getting to the function in the query editor and I wrote the following code:

= Table.AddColumn(#"Replaced Value2", "MTD", each if [Created] <= #datetime(2016, 12, 31, 0, 0, 0) then "MTD" else if [Created] >= #datetime(2016, 12, 1, 0, 0, 0) then "MTD" else null )

The column "Created" shows when the lead has been created, I wnat to create a new column where it says MTD if the lead has been created in december (for sake of the example, once the function will be set I'd like to have the MTD reflecting the current date). Then, once i'll have the column filled with MTD and null values, I can insert a slicer in the canvas where the user can switch the MTD view as he wants.

Thanks again for the great support.

Cheers, 

Andrea

[Anonymous · Accepted solution]

Hi again @tringuyenminh92 and @parry2k,

I made it to find out the formula to place in the query editor:

= Table.AddColumn(#"Replaced Value2", "MTD", each if [Created] = Date.Month(DateTime.LocalNow()) then "MTD" else null)

Thanks anyway for the help!

[parry2k]

Hey @Anonymous,

There are vaious DAX function to achieve this but first thing,  do you have continuous calendar table which is required to use DAX time intelligence functions. 

Here are few links:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

https://msdn.microsoft.com/en-us/library/ee634560.aspx?f=255&MSPPError=-2147217396

Another post on the forums

https://community.powerbi.com/t5/Desktop/DAX-YTD-and-MTD-questions/td-p/8186