Forum Discussion

fkhairaz's avatar
fkhairaz
New Member
8 years ago
Solved

How to Create a Custom Date Range

Hi,

 

What we need is, suppose a client select's Jan

 

We need to have a Table, which will have all the dates from Jan 2018 ( And -1 Year )

Which would be from Jan 2017 - Jan 2018

 

With All Dates, and The sales amount next to each date.

example:

1-1-17   |  10,000

1-2-17   |  20,000

2-2-17   |  20,000

2-10-17   |  20,000

2-12-17   |  20,000

1-1-18 | 30,000

 

 

We used this

calDatesInPeriod = CALCULATE(sum(FactResellerSales[SalesAmount]),DATESINPERIOD(DimDate[FullDateAlternateKey],DATE(2007,01,01),-1,YEAR))

 

But does not work, we do not get the sales for every date, we get the consolidated date.

 

Thanks,

Faiyaz

 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    I believe what you are seeking can be done.  Might take a few tries but here is the approach I came up with:

     

    Firstly you need selection date table.  This table should not have any relationship with any other table in your data model.  This table only needs 3 columns:

    1. Date for selection (1 row per day)
    2. Month for Slicer
    3. Start Date of Selection Period (i.e. for the row 25th of January 2018 you might hold the value 1st January 2017)

     

    You should be able to build this date table in the same manner you would any other and use Power Query code to generate all of the rows and columns.

     

    Now you need a formula that constrains itself based on this date table, but produces a null result if its value falls out your slicer date range.  I'd use something like this to achieve that result:

     

    Invoice Sum = VAR startDate = FIRSTDATE('DateSelection'[StartPeriodDate])
VAR endDate = LASTDATE('DateSelection'[SlicerDate])
RETURN

IF(
	CALCULATE(
		COUNTROWS('YourInvoiceTable'),
		'YourInvoiceTable'[InvoiceDate] >= startDate,
		'YourInvoiceTable'[InvoiceDate] <= endDate
	) > 0,
	SUM('YourInvoiceTable'[InvoiceAmount])
)

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I believe what you are seeking can be done.  Might take a few tries but here is the approach I came up with:

     

    Firstly you need selection date table.  This table should not have any relationship with any other table in your data model.  This table only needs 3 columns:

    1. Date for selection (1 row per day)
    2. Month for Slicer
    3. Start Date of Selection Period (i.e. for the row 25th of January 2018 you might hold the value 1st January 2017)

     

    You should be able to build this date table in the same manner you would any other and use Power Query code to generate all of the rows and columns.

     

    Now you need a formula that constrains itself based on this date table, but produces a null result if its value falls out your slicer date range.  I'd use something like this to achieve that result:

     

    Invoice Sum = VAR startDate = FIRSTDATE('DateSelection'[StartPeriodDate])
VAR endDate = LASTDATE('DateSelection'[SlicerDate])
RETURN

IF(
	CALCULATE(
		COUNTROWS('YourInvoiceTable'),
		'YourInvoiceTable'[InvoiceDate] >= startDate,
		'YourInvoiceTable'[InvoiceDate] <= endDate
	) > 0,
	SUM('YourInvoiceTable'[InvoiceAmount])
)