Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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!
Hi @Anonymous,
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
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!
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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |