Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
Thanks in advance
I have requirements for creating measures & am attaching the sample data
Below is the scenario
When end-user select the period slicer data should have to display from beginning to end before the month period
example :
Slicer Selected 201910 But Revenue Till 201909 Excluding Flag 0 | Slicer Selected 201910 But Revenue for Only 201910 Excluding Flag 0 | Current Revenue - Previous Revenue | Slicer Selected 201910 But Revenue for Only 2019 Excluding Flag 0 | ||
Account No | CheckMark | Previous Revenue | Current Revenue | Current Revenue - Previous Revenue | Current Year Revenue |
M180000 | D | 6735672 | 808705 | -5926967 | 7348730 |
M180000 | G | 5889915 | 1497192 | -4392723 | 7387107 |
M180000 | I | 11025213 | 1720142 | -9305071 | 12163317 |
M180000 | J | 2805103 | 1464135 | -1340968 | 4269238 |
M180000 | O | 9894515 | 1054724 | -8839791 | 10326494 |
M180000 | P | 10032185 | 1097395 | -8934790 | 10660666 |
M180000 | Q | 3049591 | 442504 | -2607087 | 3436303 |
M180000 | S | 9155182 | 610199 | -8544983 | 9412998 |
M180000 | T | 7806320 | 129364 | -7676956 | 7228525 |
M180000 | Y | 9040412 | 480203 | -8560209 | 9471635 |
M843782 | A | 12411248 | -12411248 | 11772958 | |
M843782 | B | 12016012 | -12016012 | 11632525 | |
M843782 | C | 11708902 | -11708902 | 11452739 | |
M843782 | D | 14664253 | 368194 | -14296059 | 14524855 |
M843782 | F | 6567095 | -6567095 | 5792689 | |
M843782 | H | 6703750 | -6703750 | 6379565 | |
M843782 | R | 9880054 | -9880054 | 9442935 | |
M843782 | W | 6514577 | -6514577 | 6289049 | |
Total | 155899999 | 9672757 | -146227242 | 158992328 |
Here are the Challenges am facing kindly help me to get the measure or the suggestion
Regards
Chetan
Hi,
You may download my solution PBI file from here.
Hope this helps.
@Ashish_Mathur @TomMartens @BhaveshPatel
It's almost a week time am not able to achieve the results what am organization is looking into
refer to the above image
if I select the period for 201908 that should aggregate from day 1 when the data is starting till the previous month-end which is the end date of month 2019-01-31. it's my previous month calculation
Regards,
Chetan K
@chethan no problem, send me pbix file with sample data and expected output and will do my best. You can send it by email that is in my signature below.
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.
@chethan please check the solution I sent you by email, if it works, mark this as accepted solution and Kudos are welcome. I will post the measure once you confirm that solution is working as expected.
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.
Hey @chethan ,
please let me recommend this article as this is about time patterns using DAX: https://www.daxpatterns.com/time-patterns/
First I created a dedicated calendar table using this DAX statement:
calendar =
var DateStart = MIN('Data'[Period Date])
var DateEnd = MAX('Data'[Period Date])
return
ADDCOLUMNS(
CALENDAR(DateStart , DateEnd)
, "Year-MM" , FORMAT(''[Date] , "YYYY-MM")
, "Year" , FORMAT(''[Date] , "YYYY")
)
Then I created this base measure
Total Revenue = SUM(Data[Revenue])
Based on the above I excluded the flag 0 using this DAX:
Total Revenue w/o 0 =
CALCULATE(
[Total Revenue]
, 'Data'[Flag] <> 0
)
I used this DAX to create the revenue for the previous period, of course also without the flag 0:
Total Revenue w/o 0 - prev Month =
CALCULATE(
[Total Revenue w/o 0]
, PREVIOUSMONTH('calendar'[Date])
)
All of this allows me to create this report:
Just create the finall measure that calculates the difference between the current and the previous period.
Based on the data you provided and the default behavior DAX is treating nulls, I'm wondering if a BLANK flag should be treated as 0 (this is what happend by default).
Regards,
Tom
Hey @chethan ,
please provide the DAX and describe the issue in more detail.
Consider to share your pbix or create a pbix that contains sample data, but still represents your data model. Upload the pbix to onedrive or dropbox. If you use an Excel file to create the sample data, upload the Excel file as well.
Regards,
Tom
User | Count |
---|---|
115 | |
94 | |
87 | |
76 | |
65 |
User | Count |
---|---|
138 | |
113 | |
110 | |
98 | |
93 |