Mirroring data from the same field

[Alexandra_B]

Hello.

I have two fields (Date and Time, Carbon). For both, I have records since Aug 2020 (and a tiny bit of July 20).

I would like to create two visuals to compare Aug 2020 - July 2021 and June 2021 - present , or to mirror Jan-July 2021 to Jan-July 2020 and create a yearly comparision.
Will that be posible?

I have tried to filter the date, but somehow I got mixed up and gave up.

Thank you.

[v-cgao-msft · Accepted solution]

Hi @Alexandra_B ,

Please check the PBIX file, you can set filters for individual visuals.

Or use the SAMEPERIODLASTYEAR() , DATESINPERIOD() function to compare the same period last year.

Compare equivalent periods in DAX - SQLBI

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[v-cgao-msft · Accepted solution]

Hi @Alexandra_B ,

Please check the PBIX file, you can set filters for individual visuals.

Or use the SAMEPERIODLASTYEAR() , DATESINPERIOD() function to compare the same period last year.

Compare equivalent periods in DAX - SQLBI

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[Alexandra_B]

I have managed to do it with DATESBETWEEN and then filtered the visual at the latest level of data (year, month ; removed day and quarter) (used the fork to filter)   

co2 19-20 =
                CALCULATE (
                                    SUM( 'table_hided_name'[kg co2]),
                                    DATESBETWEEN( 'table_hidden_name'[Date], "01/07/2019", "30/06/2020")
                                     )

although is not working for another table. I am attaching a snip of the error. Same formula, changed the tables and data that needs calculation.     

☹️

🙈

[Alexandra_B]

All sorted now, thank you everyone

co2 21 22 =

             CALCULATE

                               (

                                'table_hidden_name'[tonnes co2 main],

                                 DATESBETWEEN('table_hidden_name'[Date and time].[Date], DATE(2021, 07, 01),DATE(2022, 06, 30)

))

[Alexandra_B]

Hello Gao,

Thank you for your reply.

I have tried to filter the date, but it doesn`t filter it in the order I need. For example, I wanted to have July 21 to June 21 and July 21 to June 22 continuous, and instead I had Jan - June 22 and continued with July 21 - Dec 21.
I made separate visuals for the time being, but still trying to put them together in a single visual per the period required.

[v-cgao-msft · Accepted solution]

Hi @Alexandra_B ,

Just having the month field as the x-axis won't work, it can only aggregate by month, you need to use the year+month combination for the x-axis.

If the month fields in text format are not sorted correctly, you can create a table, sort by index columns, use the month fields to create a relationship with the calendar table, and finally use the month fields in the table in the chart.

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data