Forum Discussion
Monthly Automatic Change
Hi I created a card called current month and one called previous month.
They are refreshed every day, however when the month changes can the card automatically change rather than me manually picking the next month?
dommyw277 If Month_Year column is a calculated column it won't be visible in Power Query.
So, you can create below two DAX Calulated Columns and use Month Category as a filter on the cards -
Date = DATEVALUE ( "01 " & [Month-Year] )Month Category = VAR CurrentDate = TODAY() VAR PreviousMonthDate = EDATE(CurrentDate, -1) RETURN SWITCH( TRUE(), YEAR([Date]) = YEAR(CurrentDate) && MONTH([Date]) = MONTH(CurrentDate), "Current Month", YEAR([Date]) = YEAR(PreviousMonthDate) && MONTH([Date]) = MONTH(PreviousMonthDate), "Previous Month", [Month-Year] )💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
12 Replies
Hello dommyw277,
Yes it can.
If your card is configured to use the current date, rather than a manually selected month, it will update automatically after the semantic model refreshes when the month changes.
If you are selecting the month manually now, how is the card set up? That will help determine the best approach.
Hi, its currently set to use the Month/Year column and i pick the month thats currently or previous
Add the date column from your date table to each visual as a filter and use relative date filtering. You can choose "is in this month" and "is in the last 1 calendar months". Make sure you choose calendar month and not just month.
How do i add "calendar month" as i can opnly see in the last month? I have a date hierachy
Hi dommyw277,
Below is an example of how to show current month and year -
Here, I'm showing Current Month Sales and Previous Month Sales using the Date column from the Date Table.
Current Month Sales = CALCULATE ( SUM('FactSales'[Sales Amount]), DATESMTD ( 'DateTable'[Date]) ) Previous Month Sales = CALCULATE ( SUM('FactSales'[Sales Amount]), DATEADD ( 'DateTable'[Date], -1, MONTH ) )If this is not the expectation, please share some sample data and the expected output for accurate solution.
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics EngineerI dont have a date table as such so should i create one?
My table is called Usage Details and the column is called cost and the other column is Month/Year
As per your information, you have Cost and Month-Year columns.
In Power Query, Duplicate the Month-Year column and change the data type to Date which will be 1st of the month.
Then, create a custom column to mark as "Current Month" and "Previous Month". This Custom column can be used as a filter on the Cards.
This column will automatically gets updated after every refresh.
if Date.Year([Date]) = Date.Year(DateTime.Date(DateTime.LocalNow())) and Date.Month([Date]) = Date.Month(DateTime.Date(DateTime.LocalNow())) then "Current Month" else if Date.Year([Date]) = Date.Year(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -1)) and Date.Month([Date]) = Date.Month(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -1)) then "Previous Month" else [#"Month-Year"]Example -
Result -
💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster
Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer
Hi dommyw277
Yes, instead of hardcoding month you can use a measure based on TODAY() or using time intelligence DAX functions. Based on the refresh configured card visuals values will update
Current Month =
CALCULATE([Value], MONTH(DateColumn)=MONTH(TODAY()), YEAR(DateColumn)=YEAR(TODAY()))
Previous Month =
CALCULATE([Value], MONTH(DateColumn)=MONTH(EOMONTH(TODAY(),-1)), YEAR(DateColumn)=YEAR(EOMONTH(TODAY(),-1))
