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
|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|
Here are the Challenges am facing kindly help me to get the measure or the suggestion
You may download my solution PBI file from here.
Hope this helps.
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
@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.
@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.
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).
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.
Click here to read more about the December 2021 Updates!
Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.
Mark your calendars and join us for our next Power BI Dev Camp!