Calculate a Variation and Percent Variation by a Date Slicer

[Anonymous]

Hi Comunity

I need to to create a meassure or a column to calculate the Variation and the Percent Variation between a period delimited by a Date Slicer.

The Table that need to take the data is:

Table[Envios]

-EnvioID bigint

-FechaRecepcion Date

-TipoComprobante Varchar(2)

-NroComprobante Varchar(12)

-Subtotal Decimal

-Seguro Decimal

-Recargo Decimal

-Descuento Decimal

-Anulado Bit

The conditions are: That the Variation and the percent it will be between yesterday and the month above yesterday (i.e: 26/08/2019 - 26/07/2019). 

Things to take note of are: The days that this will evaluate are from Monday to Friday the other days need to be take as Friday (Saturday and Sunday) and the operation will need to be: SUBTOTAL+SEGURO+RECARGO-DESCUENTO.

The filters will be: Anulado = 0, TipoComprobante IN ('CP','RE').

If its negative then it need to be shown as well and the percent too.

I hope someone can help me.

Thank you so much in advance.

[dax]

Hi MarianoD,

If possible, could you please inform me more detailed information(such as your sample data and your expecting output)? Then I willl help youy more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

[Anonymous]

Uhmmm im using a Database to bring the data to here and the database is very long to uploaded it... i can show you what i need to achive because my boss do a "similar" dashboard with Visual Basic and im migrating that to Power BI.

The Expected data that i need to achive is something like that (marked in blue rectangle):

I just investigate and i saw that with a DATEADD function i can compare those data from a month prior or so but i have some error when i try to view the data it says something like the days doesnt match between the calculations (The Error is Error Message: MdxScript(Model) (4, 53) Calculation error in measure: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion)...

Here i can show you in SQL how i can calculate it with a very complex query (i have help to calculate it too) and how i need to expect to show it in POWER BI

I hope that it can be of some help.

Right Now: I create a new table with a period from 01/01/2000 till 31/12/2050 then that dates are having a relation to my Envios.FechaRecepcion. The DATEADD funcion it works if i use that table but it doesn't work fine (it showns the same data as my other meassure)... here i will put an image of all the things that i use...

First the measure that calculate the Total Sales of a particular day:

Second the meassure that i do to calculate the Total Sales of Last Month:

And Last the Table View that it display what are the data that gives me the 2 meassures:

Here i can shown you all the relations that my report are having so you can figure how are working the report (in the blue rectangle it displays the 2 tables that i need to perform the thing that i want to calculate).

Thank you for your time.