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 everyone,
For my Power BI Desktop file, I am using Connx tables through a odbc connction. Sales for every year are stored in separate tables. For example, the 2018 table is labelled as DTYYMM2018, 2017 is labelled as DTYYMM2017. I have a Date table with a relationship to my Sales tables.
The users have requested to have the current month sales in one visual and they would like to see the same month in the previous year in one visual (ex. June 2018 sales with June 2017 sales). Please see the following screenshot for the visualization.
I have placed a visual level filter with the month number but the issue is that every month, I need to select the next month number manually and there are over 40 reports. I have found a DAX formula which can identify current month.
CurrentMonth 2018 =
IF (YEAR (DTLMMY2018[InvoiceDate]) = YEAR ( TODAY () )
&& MONTH ( DTLMMY2018[InvoiceDate]) = MONTH ( TODAY () ),
"Yes",
"No")
This formula works well for the current year but does not allow me to show the previous year and same month in a single visualization. Please see the following screenshot with the formula applied. It will show that current month sales and teh whole year sales from the previous year.
Can anyone advise a DAX formula which can provide the current month number that can be applied for any year as opposed to only the current year?
Thank you,
Jessica
Hi Jessica,
What are the formulas you used? I would suggest you use a slicer in "Relative" mode and use the "SAMEPERIODLASTYEAR" in the formula.
Best Regards,
Dale
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |