Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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:
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]) )
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:
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]) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |