Time Intelligent with slicer selection

[chethan]

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 

Raw Data

Regards

Chetan

[Ashish_Mathur]

Hi,

You may download my solution PBI file from here.

Hope this helps.

[chethan]

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

Hi @parry2k ,

Can you help with this issue if you can

Thanks,

Chetan K

[parry2k]

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

@parry2k shared Thanks

[parry2k]

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

[TomMartens]

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

[chethan]

@TomMartens but still I have an issue with the DAX

[TomMartens]

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