Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Guys,
I am trying to do a Count of X (submit dates) of the previous month.
I have a date table linked to the dates I am interested in in the fact table: the relationship is okay and I can drag and drop dates from the date table into the canvas, with the corresponding submit dates.
The functions Dateadd and previousmonth are both not working though.
Whenever I try to use PREVIOUSMONTH, the result is blank. When I put a relative date slicer in the canvas and tell it to slice for the last calendar month, PREVIOUSMONTH is doing a correct count, but for the month of july.
What to do to get it to work?
Solved! Go to Solution.
Hi @Anonymous ,
In your scenario, we cannot use previousmonth or dateadd. As for both fucntion, we can get the date by the current row, However in a card visual, there is no filter like a table visual. That's why we got incorrect result by that way. So we can use EDATE to work on it that the logic is slmilar to your work around.
previous = VAR a = FORMAT ( EDATE ( TODAY (), -1 ), "yyyymmm" ) RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', FORMAT ( 'Table'[date], "yyyymmm" ) = a ) )
Hi @Anonymous ,
One sample for your reference. We should add the date column of the fact table to the visual to work on it. Then both Dateadd and PREVIOUSMONTH work well.
Measure = CALCULATE(SUM('Table'[value]),PREVIOUSMONTH('Table'[date]))
Pre = CALCULATE(SUM('Table'[value]),DATEADD('Table'[date],-1,MONTH))
Pbix as attached.
Thank you very much for the reply, I see it works like a charm.
I think it was a misunderstanding from my part: I wanted to show the amount belonging to the previous month on a Card visual (last month's count). In your example, using the measure in a card visual also returns a blank.
How do I incorporate these measures in a card visual? So that one amount (that of the last month) is shown?
Hi @Anonymous ,
In your scenario, we cannot use previousmonth or dateadd. As for both fucntion, we can get the date by the current row, However in a card visual, there is no filter like a table visual. That's why we got incorrect result by that way. So we can use EDATE to work on it that the logic is slmilar to your work around.
previous = VAR a = FORMAT ( EDATE ( TODAY (), -1 ), "yyyymmm" ) RETURN CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', FORMAT ( 'Table'[date], "yyyymmm" ) = a ) )
Please check the explanation given on previousmonth at
https://community.powerbi.com/t5/Desktop/PREVIOUSMONTH/td-p/13307.
Hope that will help.
User | Count |
---|---|
87 | |
72 | |
69 | |
64 | |
54 |
User | Count |
---|---|
99 | |
91 | |
80 | |
74 | |
64 |