cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
great_AS Member
Member

Edit query to show MTD values

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

1 ACCEPTED SOLUTION

Accepted Solutions
great_AS Member
Member

Re: Edit query to show MTD values

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!

4 REPLIES 4
Super User
Super User

Re: Edit query to show MTD values

Hey @great_AS,

 

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






Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





tringuyenminh92 New Contributor
New Contributor

Re: Edit query to show MTD values

Hi @great_AS,

 

You could follow up these topic to achieve that expectation:

 

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

great_AS Member
Member

Re: Edit query to show MTD values

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

great_AS Member
Member

Re: Edit query to show MTD values

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!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 307 members 3,289 guests
Please welcome our newest community members: