cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edoardo_mussi
Resolver I
Resolver I

Unable to correctly filter by time period

I am trying to show data from a time series in an Area Chart visual filtering by time period, and need to show two visuals:

  1. Current year sales vs. Last Year Sales
  2. Last 12 months from today vs. Previous 12 months

 

 In visual n.1 I used the PARALLELPERIOD() function and it behaves as expected, showing meaningful data when performing drill-down operations.

Visual n.1Visual n.1

Screenshot_20221213_141830.png

 

When working with visual n.2 however, I can't seem to get the DAX

 formula for the measures right. All I get is a constant value across all points of the time series, which cannot be the case as data is the same from visual n.1

visual n.2visual n.2

 

 

 

 

 

 

Formulas for visual n.2 are:

12 Mesi Precedenti = 
CALCULATE(
    SUM(Fatture[Importo Pagamento]),
    DATEADD(
        DATESINPERIOD('Date'[DateKey], TODAY(), -12, MONTH),
    -12,
    MONTH)
)
Ultimi 12 Mesi = 
CALCULATE(
    SUM( Fatture[Importo Pagamento] ),
    DATESINPERIOD( 'Date'[DateKey], TODAY(), -12, MONTH )
)

To the best of my knowledge, the DATESINPERIOD() function should return all available dates in the given time period, hence why I would expect the graph to be able to display data for different levels of granularity. (Thus allowing for drill-down operations.)

 

 

I would greatly appreciate any help that comes my way.

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @edoardo_mussi ,

You can refer the following links to get it:

Same Period Last Year to Date DAX Calculation in Power BI

How to compare Last year and Current Year sales in Power BI?

If the above one can't help you, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

The problem I'm currently facing is the measure return a single value, whereas I would like it to return a single table column with all sales data from the period selected in the formula.

Sample data would be as follows:

DateKey            DOC_ID            SalesAmount

01/01/2022       A00001            500.00

01/01/2022       A00002            625.00

01/01/2022       A00003            755.00

02/01/2022       A00004            315.00

03/01/2022       A00005            146.00

05/01/2022       A00006            574.00

 

Assuming the table above mirrors the time period I want to select, the measure 2915.50 (sum of all values in the period) but will not be able to show data for the single days.(all dates in the resulting table will swho 2915.50 as value)

 

Current outcome is:

DateKey            Sales

01/01/2022      2915.50

02/01/2022      2915.50

03/01/2022      2915.50

05/01/2022      2915.50

 

Desired outcome is:

DateKey            Sales

01/01/2022      1880.00

02/01/2022      315.00

03/01/2022      146.00

05/01/2022      574.50

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors