cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chethan
Resolver III
Resolver III

Time Intelligent with slicer selection

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 0Slicer Selected 201910 But Revenue for Only 201910 Excluding Flag 0Current Revenue - Previous RevenueSlicer Selected 201910 But Revenue for Only 2019 Excluding Flag 0
Account NoCheckMarkPrevious RevenueCurrent Revenue Current Revenue - Previous RevenueCurrent Year Revenue
M180000D6735672808705-59269677348730
M180000G58899151497192-43927237387107
M180000I110252131720142-930507112163317
M180000J28051031464135-13409684269238
M180000O98945151054724-883979110326494
M180000P100321851097395-893479010660666
M180000Q3049591442504-26070873436303
M180000S9155182610199-85449839412998
M180000T7806320129364-76769567228525
M180000Y9040412480203-85602099471635
M843782A12411248 -1241124811772958
M843782B12016012 -1201601211632525
M843782C11708902 -1170890211452739
M843782D14664253368194-1429605914524855
M843782F6567095 -65670955792689
M843782H6703750 -67037506379565
M843782R9880054 -98800549442935
M843782W6514577 -65145776289049
Total 1558999999672757-146227242158992328

 

Here are the Challenges am facing kindly help me to get the measure or the suggestion 

 

Raw Data

 

Regards

Chetan

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

You may download my solution PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur @TomMartens @BhaveshPatel 

 

It's almost a week time am not able to achieve the results what am organization is looking into 

 

Period Slicer.PNG

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

 

 

Hi @parry2k ,

 

Can you help with this issue if you can

 

 

 

Thanks,

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.






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.





@parry2k shared Thanks

@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.

 

 






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.





TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens but still I have an issue with the DAX

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors