Forum Discussion
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
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
- parry2k
Super User
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
- tringuyenminh92
Memorable Member
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
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
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!
