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.
Hi,
I have a dashbord table with a daily comparaison between two month.
ex: PrevMthByDay = CALCULATE([TotalRevenue],DATEADD(tbDate[Date],-1,MONTH))
If the current month is May (31 days) then it will be compare to April (30 days). In the dashboard display table, in theprevious month (april) column, the value is repeated.
ex :
Date Current Month Previous Month
May 30 1000$ 500$ (April 30)
May 31 2000$ 500$ (April 31 doesn't exist, the previous value is repeated)
TOTAL 3000$ 500$ (the Total is good)
Would it be possible to have blank, zero or N/A ?
Thanks for you help
Solved! Go to Solution.
Interesting, that just means that the month you selected has more day than the prvious month so the total row is getting trapped as well. We can fix it by only doing the check when we are looking at a single date value like so:
Prior Month Paid Amount =
VAR _MonthDay =
DAY ( SELECTEDVALUE ( DATES[Date] ) )
VAR _PMMonthDay =
CALCULATE (
DAY ( SELECTEDVALUE ( DATES[Date] ) ),
DATEADD ( DATES[Date], -1, MONTH )
)
RETURN
IF (
_MonthDay > _PMMonthDay && HASONEFILTER(DATES[Date]),
BLANK (),
CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
)
The addition of the && HASONEFILTER(DATES[Date]) in the IF only returns BLANK() if we are on a single day line.
Hello @SylBell01
You can, I have a column in my Dates table that is the [Day of Month Number]. I can use that and compare the current day of month number to the prior month day of month number and if the current is > the prior show blank.
Prior Month Paid Amount =
VAR _MonthDay =
MAX ( DATES[Day of Month Number] )
VAR _PMMonthDay =
CALCULATE (
MAX ( DATES[Day of Month Number] ),
DATEADD ( DATES[Date], -1, MONTH )
)
RETURN
IF (
_MonthDay > _PMMonthDay,
BLANK (),
CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
)
You can see the amount works for 3/28 but is blank for 3/29 - 3/31:
If you don't want to mess with adding a column to your calendar table you can also do it using the DAY function and comparing those instead:
Prior Month Paid Amount =
VAR _MonthDay =
DAY ( SELECTEDVALUE ( DATES[Date]) )
VAR _PMMonthDay =
CALCULATE (
DAY ( SELECTEDVALUE ( DATES[Date]) ),
DATEADD ( DATES[Date], -1, MONTH )
)
RETURN
IF (
_MonthDay > _PMMonthDay,
BLANK (),
CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
)
Interesting, that just means that the month you selected has more day than the prvious month so the total row is getting trapped as well. We can fix it by only doing the check when we are looking at a single date value like so:
Prior Month Paid Amount =
VAR _MonthDay =
DAY ( SELECTEDVALUE ( DATES[Date] ) )
VAR _PMMonthDay =
CALCULATE (
DAY ( SELECTEDVALUE ( DATES[Date] ) ),
DATEADD ( DATES[Date], -1, MONTH )
)
RETURN
IF (
_MonthDay > _PMMonthDay && HASONEFILTER(DATES[Date]),
BLANK (),
CALCULATE ( [paid amount], DATEADD ( DATES[Date], -1, MONTH ) )
)
The addition of the && HASONEFILTER(DATES[Date]) in the IF only returns BLANK() if we are on a single day line.
Wow it works perfectly. Thank you very much.
Would it be possible to ask you another question related to a similar problem ?
You should post it as a new topic that way it gets more people to look at it. Someone besides me may have a better answer for your next question.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |